CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Matt
Matt

Reputation: 1374

Group SUM data in 24 hour chunks

I have data in Microsoft SQL Server 2008 that I need to SUM. The catch is I need to group the sums by a 24 hour period. The 24 hour period is from 3:00pm one day to 3:00pm the 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 that should be 54. I have the following query but that groups everything from midnight to midnight instead of 3:00 pm to 3:00 pm

SELECT dateadd(day,datediff(day,0, dateadd(hh, 0, DateExited) ),0) As SummaryDate, SUM(Value) as s1
FROM Test
where DateExited BETWEEN dateadd(year,datediff(year,0,GETDATE()),0) AND GetDate()
GROUP BY dateadd(day,datediff(day,0, dateadd(hh, 0, DateExited) ),0)
ORDER BY SummaryDate

Upvotes: 1

Views: 1905

Answers (4)

Russell Fox
Russell Fox

Reputation: 5435

You could create a function to return the needed value given an date input parameter:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetValueSum 
(
    @StartDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @ValueSum INT
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

-- Set the starting time to 3 PM on the datetime sent in:
SET @StartDateTime = DATEADD(hh, 15, CAST(CAST(@StartDate AS DATETIME) AS DATETIME))

-- Set the end time to one day later, minus 3 milliseconds (this smallest unit sql server can use)
SET @EndDateTime = DATEADD(ms, -3, DATEADD(dd, 1, @StartDateTime))

SELECT @ValueSum = SUM(Value)
FROM dbo.test
WHERE DateExited BETWEEN @StartDateTime AND @EndDateTime

RETURN @ValueSum

END
GO

Upvotes: 0

Andomar
Andomar

Reputation: 238126

You could add minus 15 hours, and then cast the result to date:

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

Giving you a group by like:

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

Upvotes: 1

Chris Shaffer
Chris Shaffer

Reputation: 32575

Subtract 15 hours from your date/time value should give you the results you are looking for.

Also, with SQL 2008, you can convert datetimes to dates instead of adding days to 0.

SELECT CONVERT(DATE, DATEADD(hour, -15, DateExited)) As SummaryDate, SUM(Value) as s1
FROM Test
WHERE DATEADD(hour, -15, DateExited) BETWEEN @StartDate AND @EndDate
GROUP BY CONVERT(DATE, DATEADD(hour, -15, DateExited))
ORDER BY SummaryDate

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The easiest way is to cast to date:

SELECT cast(SummaryDate as date), SUM(Value) as s1
FROM Test
where DateExited BETWEEN dateadd(year,datediff(year,0,GETDATE()),0) AND GetDate()
GROUP BY cast(SummaryDate as date)
ORDER BY 1

Upvotes: 0

Related Questions