sql - Group SUM data in 24 hour chunks -

i have data in microsoft sql server 2008 need sum. catch need group sums 24 hour period. 24 hour period 3:00pm 1 day 3:00pm next day.

for example

dateexited, value

1/1/2012 15:00, 5

1/1/2012 15:04, 6

1/1/2012 17:00, 7

1/2/2012 00:00, -5

1/2/2012 09:00, 10

1/2/2012 15:00, 31

the sum of should 54. have following query groups midnight midnight instead of 3:00 pm 3:00 pm

select dateadd(day,datediff(day,0, dateadd(hh, 0, dateexited) ),0) summarydate, sum(value) s1 test dateexited between dateadd(year,datediff(year,0,getdate()),0) , getdate() group dateadd(day,datediff(day,0, dateadd(hh, 0, dateexited) ),0) order summarydate 

you add minus 15 hours, , cast result date:

select  cast(dateadd(hour,-15,'2012-05-06 14:30') date) -- 2012-05-05 select  cast(dateadd(hour,-15,'2012-05-06 15:30') date) -- 2012-05-06 

giving group by like:

group cast(dateadd(hour,-15,'2012-05-06 03:30') date)