CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sergeistz
Sergeistz

Reputation: 55

Summing Row in SQL query for time range

I'm trying to group a large amount of data into smaller bundles.

Currently the code for my query is as follows

SELECT [DateTime] ,[KW] FROM [POWER] WHERE datetime >= '2014-04-14 06:00:00' and datetime < '2014-04-21 06:00:00' ORDER BY datetime

which gives me

DateTime KW 4/14/2014 6:00:02.0 1947 4/14/2014 6:00:15.0 1946 4/14/2014 6:00:23.0 1947 4/14/2014 6:00:32.0 1011 4/14/2014 6:00:43.0 601 4/14/2014 6:00:52.0 585 4/14/2014 6:01:02.0 582 4/14/2014 6:01:12.0 580 4/14/2014 6:01:21.0 579 4/14/2014 6:01:32.0 579 4/14/2014 6:01:44.0 578 4/14/2014 6:01:53.0 578 4/14/2014 6:02:01.0 577 4/14/2014 6:02:12.0 577 4/14/2014 6:02:22.0 577 4/14/2014 6:02:32.0 576 4/14/2014 6:02:42.0 578 4/14/2014 6:02:52.0 577 4/14/2014 6:03:02.0 577 4/14/2014 6:03:12.0 577 4/14/2014 6:03:22.0 578 . . . . 4/21/2014 5:59:55.0 11

Now there is a reading every 10 seconds from a substation. Now I want to group this data into hourly readings.

Thus 00:00-01:00 = sum([KW]] for where datetime >= '^date^ 00:00:00' and datetime < '^date^ 01:00:00'

I've tried using a convert to change the datetime into date and time field and then only to add all the time fields together with no success.

Can someone please assist me, I'm not sure what is right way of doing this. Thanks

ADDED

Ok so the spilt between Datetime is working nicely, but as if I add a SUM([KW]) function SQL gives an error. And if I include any of the group functions it also nags.

Below is what works, I still need to sum the KW per the grouping of hours.

I've tried using Group By Hour and Group by DATEPART(Hour,[DateTime])

Both didn't work.

SELECT DATEPART(Hour,[DateTime]) Hour ,DATEPART(Day,[DateTime]) Day ,DATEPART(Month,[DateTime]) Month ,([KVAReal]) ,([KVAr]) ,([KW]) FROM [POWER].[dbo].[IT10t_PAC3200] WHERE datetime >= '2014-04-14 06:00:00' and datetime < '2014-04-21 06:00:00' order by datetime

Upvotes: 2

Views: 348

Answers (2)

Sergeistz
Sergeistz

Reputation: 55

Ok so here is the solution that worked for me.

Declare @Begin Varchar(60), @End Varchar(60) Set @Begin = '2014-05-22 06:00:00' Set @End = '2014-06-01 06:00:00'

SELECT ID='10T' ,DATEPART(month,[DateTime]) Month ,DATEPART(day,[DateTime]) Day ,DATEPART(hour,[DateTime]) as Hour ,avg([kw]) hourly_kWh_10T ,avg([KVAr]) hourly_kVarh_10T ,avg([KVAReal]) hourly_kVAh_10T ,(case when(DATEPART(hour,[DateTime]) <=6 and DATEPART(hour,[DateTime]) >18) then 'D' else 'N' end) shift FROM [POWER] where DateTime <= @Begin and DateTime > @End group by DATEPART(Hour,[DateTime]),DATEPART(Day,[DateTime]),DATEPART(Month,[DateTime])

This code gave me this result I was looking for. I also include a variable starting point to reduce the input for different dates. + added a if function (Case when) to determine if the power was consumed during Day or Night shift.

ID Month Day Hour hourly_kWh_10T hourly_kVarh_10T hourly_kVAh_10T shift 10T 5 22 6 269.278551 80.771587 294.038997 D 10T 5 22 7 241.213296 75.991689 268.085872 D 10T 5 22 8 283.925 93.302777 319.211111 D 10T 5 22 9 11.763888 31.313888 36.372222 D 10T 5 22 10 215.947222 69.702777 243.541666 D 10T 5 22 11 1895.816666 396.805555 1948.061111 D 10T 5 22 12 2385.486033 513.589385 2447.648044 D 10T 5 22 13 440.737569 126.209944 475.049723 D 10T 5 22 14 737.158333 183.05 775.763888 D 10T 5 22 15 41.961111 38.086111 67.277777 D 10T 5 22 16 11.875 30.577777 35.736111 D 10T 5 22 17 11.263888 27.563888 32.497222 D 10T 5 22 18 11.104956 26.381924 31.323615 N 10T 5 22 19 11.648936 28.813829 34.015957 N 10T 5 22 20 229.819944 75.227146 268.432132 N 10T 5 22 21 300.597222 92.661111 340.413888 N 10T 5 22 22 494.575 124.358333 527.183333 N 10T 5 22 23 922.244444 190.472222 954.961111 N 10T 5 23 0 2445.908333 516.008333 2507.613888 N 10T 5 23 1 1399.147222 317.380555 1446.786111 N 10T 5 23 2 258.097222 81.641666 288.308333 N 10T 5 23 3 258.480555 79.694444 285.488888 N 10T 5 23 4 262.108333 82.455555 290.261111 N 10T 5 23 5 270.830555 82.030555 297.011111 N 10T 5 23 6 570.836111 151.930555 606.05 D 10T 5 23 7 10.580555 24.488888 29.233333 D

Upvotes: 1

Andomar
Andomar

Reputation: 238296

The function convert(varchar(13), getdate(), 120) displays 2014-06-03 16. You can use that to group by the hour:

SELECT  convert(varchar(13), [DateTime], 120) as dt
,       SUM(KW) as SumKwPerHour
FROM    POWER
WHERE   [DateTime] >= '2014-04-14 06:00:00' 
        AND [DateTime]< '2014-04-21 06:00:00'
GROUP BY
        convert(varchar(13), [DateTime], 120)
ORDER BY
        dt

Upvotes: 1

Related Questions