CODEWITHSUNDEEP
sqlsql-server-2008
eltel2910
eltel2910

Reputation: 345

Sum Totals for Every 1/2 Hour

I have 2 columns: [StartDT] and [EndDT], they are datetime format like such: 2014-05-20 12:45:18.383

I need to get things in terms of total sum of the difference between [StartDT] and [EndDT] every 1/2 hour. So basically each row of my output would be the sum of the differences for a particular 1/2 period like: 00:00 - 34 Seconds, next row: 00:30 - 23 Seconds.

I was able to create the code to do this once and it looks like this:

 SELECT  sum(DATEDIFF ( s , [StartDT] , [EndDT] )) as SumTotal
 FROM [Radio].[dbo].[Sheet1]
 WHERE DATEPART(DAY, StartDT) = 1 and DATEPART(HOUR, StartDT) between 0 and 1 and DATEPART(MI, StartDT) between 0 and 30;

The code will look at the table and find the first 30 minute period of the first hour of the first day. Now how would I automate this to iterate through the entire table. I know how I would do it in C, but I do not have much experience with SQL for sub quires or Common Table Expressions (CTE) .

Upvotes: 0

Views: 1091

Answers (1)

bwperrin
bwperrin

Reputation: 692

The trick is calculating your HalfHour value, then GROUP BY - try:

SELECT dateadd(mi, (datediff(mi, 0, StartDT) / 30) * 30, 0) as HalfHour
       , sum(DATEDIFF ( s , [StartDT] , [EndDT] )) as SumTotal
FROM Radio.dbo.Sheet1
GROUP BY dateadd(mi, (datediff(mi, 0, StartDT) / 30) * 30, 0) 
ORDER BY 1

Upvotes: 1

Related Questions