0

I have a sql server table with meter values which are read every minute each day which should give me 1440 records per meter per day. Sometimes the data doesn't come in every minute and as such a row can be missed. How would I insert a row with a 0 or null value to complete the intervals.

Example

Datetimecolum        kWh 
2017-01-01 12.00 AM  64.00
2017-01-01 12.01 AM  0.02
2017-01-01 12.03 AM  2.32

I would like to populate a row for 12.02 AM with 0 or null value to indicate the reading was not received. Any help would be appreciated.

Thanks

0
1

A quick one using Itzik ben gan tally table.

DECLARE @date datetime='20180327 00:00'

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT TOP (1440) dateadd(minute,n,@date) n
FROM Tally

You can left join this with you meter reads table.

0

Throw good sample data which cover 2-3 test cases.

So accordingly i correct my script.

First create one permanent table which contain time only.

This is one time creation.

CREATE table OneTimeTimeinterval(Timeinterval time)
;with CTE as
(
select cast('12:00 AM' as time) Timeinterval,1 rn
union ALL
select cast(dateadd(minute,1, Timeinterval)as time),rn+1
from cte
where rn<24*60

)
insert into OneTimeTimeinterval
select Timeinterval from CTE OPTION(MAXRECURSION 0)

Final Script,

    declare  @t table(Datetimecolum datetime,kWh float )
insert into @t VALUES
 ('2017-01-01  12:00 AM', 64.00 )
,('2017-01-01 12:01 AM',  0.02  )
,('2017-01-01 12:03 AM',  2.32  )
,('2017-02-01 12:03 AM',  0.32  )
,('2017-02-01 12:00 AM',  1.32  )

    ;

WITH CTE
AS (
    SELECT cast(Datetimecolum AS DATE) Datecolum
        ,min(cast(Datetimecolum AS TIME)) Mintime
        ,max(cast(Datetimecolum AS TIME)) MaxTime
    FROM @t
    GROUP BY cast(Datetimecolum AS DATE)
    )


SELECT Datecolum
    ,Timeinterval
    ,isnull(t.kWh, 0) kWh
FROM cte c
OUTER APPLY (
    SELECT *
    FROM OneTimeTimeinterval a
    WHERE Timeinterval BETWEEN c.Mintime
            AND c.MaxTime
    ) ca
LEFT JOIN @t t ON t.Datetimecolum = DATEADD(minute, datepart(minute, Timeinterval), cast(Datecolum AS DATETIME))
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.