asked on

sql server group by every 2hrs

Hello I have this code, which groups by the hour.

SELECT CAST(start_date as date) AS ForDate,
       DATEPART(hour,start_date) AS OnHour,
       COUNT(*) AS Totals , sum(Volume) 
FROM #temp wHERE ID in (1,2,3,4) 
GROUP BY CAST(start_date as date),
       DATEPART(hour,start_date)

Open in new window

Does anyone know how I can modify it to group by every 2hrs?

Pawan Kumar

Hi,

Very quick try ...

SELECT CAST(start_date as date) AS ForDate,
       (DATEPART(hour,start_date)+1) AS OnHour,
       COUNT(*) AS Totals , sum(Volume) 
FROM #temp wHERE ID in (1,2,3,4) 
GROUP BY CAST(start_date as date),
       (DATEPART(hour,start_date)+1)

Open in new window

Scott Pletcher

Not sure of the exact details of how you want to do this, but something like:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, start_date) / 2 * 2, 0) AS DateAndHour,
COUNT(*) AS Totals , sum(Volume)
FROM #temp wHERE ID in (1,2,3,4)
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, start_date) / 2 * 2, 0)

barkome

ASKER

Thanks, I forgot to mention that I would like to group by every 2hrs from 5am

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial