I currently have a sql-server database with data revolving around watt generation per second. The watt data is normally saved in second intervals, but sometimes there are inconsistencies where it is saved by a minute interval instead. Thus, instead of having 60 seconds of data saved, only one row for that minute interval is created.
When I retrieve watt data between two time points, I want it to be consistent in seconds. If there is a minute difference between two rows, is there a way to replicate the row with the data for that recorded minute 59 times (or more if the interval is larger)?
My current query looks like this:
SELECT T1.Start_Time, T1.End_Time, T1.watts, T1.RN, T2.Start_Time,
T2.End_Time, T2.watts, T2.RN, DATEDIFF(second, T1.end_time,
T2.end_time) [span (seconds)], t2.Watts - T1.Watts [Diff],
(t2.Watts - T1.Watts)/1000 [Diff Kw]
FROM(select [Start_Time], [End_Time], watts, ROW_NUMBER() OVER (ORDER
BY watts.[Start_Time]) RN ,DATEDIFF(second, start_time, end_time)
span
from dbo.watts
where watts.SubMeter_Id = 21
AND watts.End_Time >= dateadd(S, 1538701191, '1970-01-01')
AND watts.End_Time <= dateadd(S, 1538787591, '1970-01-01')
AND DATEDIFF(second, watts.start_time, watts.end_time)>=1
) AS T1
LEFT JOIN (select [Start_Time], [End_Time], watts, ROW_NUMBER() OVER
(ORDER BY watts.[Start_Time]) RN ,DATEDIFF(second,
start_time, end_time) span
from dbo.watts
where watts.SubMeter_Id = 21
AND watts.End_Time >= dateadd(S, 1538701191, '1970-01-01')
AND watts.End_Time <= dateadd(S, 1538787591, '1970-01-01')
AND DATEDIFF(second, watts.start_time, watts.end_time)>=1
) AS T2
ON T1.RN = T2.RN-1
Sample data in Watts table:
ID | Submeter_Id | Watts | Start_Time | End_Time
3705255 | 19 | 16428779218 | 2018-09-27 07:28:26.000 | 2018-09-27 07:28:27.000
3705256 | 19 | 16428784527 | 2018-09-27 07:28:58.000 | 2018-09-27 07:28:59.000
Sample current output:
Start_Time | End_Time | watts | RN | Start_Time | End_Time | watts | RN | span (seconds) | Diff | Diff Kw
2018-10-05 01:39:23.000 | 2018-10-05 01:39:24.000 | 2858527031558 | 2362 | 2018-10-05 01:39:24.000 | 2018-10-05 01:39:25.000 | 2858527122527 | 2363 | 1 | 90969 | 90
2018-10-05 01:40:00.000 | 2018-10-05 01:41:00.000 | 2858536311254 | 2364 | 2018-10-05 01:41:00.000 | 2018-10-05 01:42:00.000 | 2858542101255 | 2365 | 60 | 5790001 | 5790
Currently, this query outputs all the rows between the two given timestamps. The time span column is usually 1 second, but sometimes the data is saved per minute instead. I can't figure out how to get the rows to be replicated if the time difference between two columns is greater than 1 second.
Any help on this would be greatly appreciated. Thank you in advance.
