DaveDB (12/12/2013)

---

Hello,

I need to write a SQL query that aggregates data based on 1 hour intervals. So, basically I would need something that lists all available times: (6:30 - 7:30), (7:30 - 8:30), (8:30 - 9:30), etc. So the INTERVALSTART and END have to be on the :30, but display the full hour. I assume I have to do something in the WHERE clause as well. The column that I am using for hour intervals is date/time.

Any help would be appreciated. Here is what I have so far...

SELECT INTERVAL_START, INTERVAL_END, AGGREGATED_VALUE

FROM TABLE A

LEFT JOIN TABLE B

ON A.logid = G.logid

WHERE convert(varchar(15), INTERVALSTART, 101) = convert(varchar(15),getdate(),101)

AND...... Start and End time

AND Item_Name IN ('Conservation Only','NJ SWAT','NJ Contact Center')

GROUP BY INTERVAL_START, INTERVAL_END, AGGREGATED_VALUE