Generate missing data from existing data

Question

I have hourly level data of a unit, with its status and its value. The status and value of the unit changes after certain intervals (not necessarily at every hour). I want to generate hourly level data from existing data. Say for example: I have input such as:

and output required is:

Please find below scripts for required input and output:

Input

SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-25' AS CDate,'22' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'2' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'5' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'8' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'11' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'13' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'16' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'20' AS CHour,1.0 AS Value

Output:

SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-25' AS CDate,'22' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-25' AS CDate,'23' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'0' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'1' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'2' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'3' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'4' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'5' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'6' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'7' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'8' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'9' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'10' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'11' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'0' AS Status,'2017-10-26' AS CDate,'12' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'13' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'14' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'15' AS CHour,0.5 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'16' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'17' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'18' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'1' AS Status,'2017-10-26' AS CDate,'19' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'20' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'21' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'22' AS CHour,1.0 AS Value
UNION ALL
SELECT '3' as ID,'CName1' AS Name,'2' AS Status,'2017-10-26' AS CDate,'23' AS CHour,1.0 AS Value

Can we see some source data with values which are not all the same? What happens if the values are different? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 13, 2017 at 11:19
@TimBiegeleisen this is the source data, and the data in the image is almost the same, except the column name. — Logical
– Logical, Commented Nov 13, 2017 at 11:58

Zorkolot · Accepted Answer · 2017-11-13 17:20:05Z

This is for SQL Server.

Here, I use a CTE that supplies a rownumber to distinguish previous date and time. The CTE selects distinct Dates cross joined to your hours format: 0 to 23.

In the select statement is the algorithm to identify previous row. I inner join the CTE to itself using this previous rownumber. By inner join this removes earlier dates prior to the first data entry.

This is subqueried. If the row was missing data, then by LEFT JOIN in the cte fields like ID, Name, and Status are NULL. The Coalesce selects the original (non-null) data, else the most recent data by rownumber.

DECLARE @temp TABLE (ID tinyint, Name varchar(100), Status tinyint, CDate date, CHour tinyint, Value decimal(12,1))
INSERT INTO @temp(ID, Name, Status, CDate, CHour, Value)
VALUES (3, 'CName1', 0, '2017-10-25', 22, 0.5)
      ,(3, 'CName1', 1, '2017-10-26',  2, 0.5)
      ,(3, 'CName1', 0, '2017-10-26',  5, 0.5)
      ,(3, 'CName1', 1, '2017-10-26',  8, 0.5)
      ,(3, 'CName1', 0, '2017-10-26', 11 ,0.5)
      ,(3, 'CName1', 1, '2017-10-26', 13 ,0.5)
      ,(3, 'CName1', 1, '2017-10-26', 16 ,1.0)
      ,(3, 'CName1', 2, '2017-10-26', 20 ,1.0)
;
WITH cte AS
(
SELECT ROW_NUMBER() OVER(ORDER BY dT.CDate2, dT.CHour2) [theOrder]
      ,*      
  FROM (
        SELECT DISTINCT T.CDate [Cdate2], dT.CHour2
          FROM @temp T 
               CROSS JOIN (SELECT 0 [CHour2] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
                           UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
                           UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 
                           UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 
                           UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 
                          ) AS dT --joins for any missing hours
       ) AS dT LEFT JOIN @temp T ON dT.Cdate2 = T.CDate AND T.CHour = dT.CHour2
)               

SELECT COALESCE(dT.ID, cte2.ID) [ID]
      ,COALESCE(dT.[Name], cte2.[Name]) [Name]
      ,COALESCE(dT.[Status], cte2.[Status]) [Status]
      ,dT.Cdate2 [Cdate]
      ,dT.CHour2 [CHour]      
      ,COALESCE(dT.[Value], cte2.[Value]) [Value]

  FROM (
        SELECT C1.*
              ,(SELECT MAX(theOrder)
                  FROM cte C2
                 WHERE C2.theOrder <= C1.theOrder AND C2.ID IS NOT NULL
               ) [maxorder]
          FROM cte C1
       ) AS dT INNER JOIN cte cte2 ON dT.maxorder = cte2.theOrder

This output matches your requested output.

Steve Ford · Accepted Answer · 2017-11-13 14:51:40Z

If you have SQL Server > 2012 you can use LEAD to find the value of the next date together with a TALLY table to generate the in-between rows:

Set-Up

SELECT 3 as ID,'CName1' AS Name,0 AS Status,CAST('2017-10-25' AS DATE) AS CDate,22 AS CHour,0.5 AS Value
INTO #ChannelData
UNION ALL
SELECT 3 as ID,'CName1' AS Name,1 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,2 AS CHour,0.5 AS Value
UNION ALL
SELECT 3 as ID,'CName1' AS Name,0 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,5 AS CHour,0.5 AS Value
UNION ALL
SELECT 3 as ID,'CName1' AS Name,1 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,8 AS CHour,0.5 AS Value
UNION ALL
SELECT 3 as ID,'CName1' AS Name,0 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,11 AS CHour,0.5 AS Value
UNION ALL
SELECT 3 as ID,'CName1' AS Name,1 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,13 AS CHour,0.5 AS Value
UNION ALL
SELECT 3 as ID,'CName1' AS Name,1 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,16 AS CHour,1.0 AS Value
UNION ALL
SELECT 3 as ID,'CName1' AS Name,2 AS Status,CAST('2017-10-26' AS DATE)  AS CDate,20 AS CHour,1.0 AS Value

Query:

;WITH Tally -- Generate Tally Table
As
(
    SELECT ROW_NUMBER() OVER (ORDER BY num.n) - 1 AS number
    FROM 
        (VALUES (1), (2),(3),(4),(5),(6),(7),(8),(9),(10)) num(n)
    CROSS APPLY
        (VALUES (1), (2),(3),(4),(5),(6),(7),(8),(9),(10)) num2(n)
    CROSS APPLY
        (VALUES (1), (2),(3),(4),(5),(6),(7),(8),(9),(10)) num3(n)
),
MyRows
As
(
    SELECT  Id, Name, Status, CDate, CHour, Value
              -- Turn Date to DateTime
            , DATEADD(HH,CHour, CAST(CDate AS DateTime)) AS FullDate
              -- Get next date time 
            , DATEADD(HH,LEAD(CHour) OVER (PARTITION BY Name ORDER BY CDate, CHour)
            , CAST(LEAD(CDATE) OVER (PARTITION BY Name ORDER BY CDate, CHour)AS DateTime))  AS NextFullDate
    FROM #ChannelData 
)
SELECT  Id, Name, [Status], 
        CAST(DATEADD(HH, number, FulLDate) AS Date) AS CDate,
        DATEPART(HH,DATEADD(HH, number, FulLDate)) AS CHour,
        Value
FROM MyRows
CROSS APPLY Tally
WHERE 
    DATEADD(HH, number, FullDate) < COALESCE(NextFullDate, DATEADD(hh, 1, FullDate)) 
ORDER BY 
    CAST(DATEADD(HH, number, FulLDate) AS Date),
    DATEPART(HH,DATEADD(HH, number, FulLDate))

Collectives™ on Stack Overflow

Generate missing data from existing data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related