Instead of GROUP BY the
LoadingDateTime
, you need to use a separate select to add a TimeGroup column to the data, which merges the data into two hour groups. You can then GROUP BY that column, and use the SUM aggregate function to combine them.
GROUPing by the raw DATETIME value will never give you what you want.
Start by getting the SELECT right and then SELECT from that with the GROUP BY.
Quote:
(select DateAdd(second,7200, '1900-01-01 00:00:00' + convert(int,(7200 * floor(datediff(HOUR, [LoadingDateTime], '1900-01-01 00:00:00' )/2)))) ReportTimestamp
could you help validate this script? I have been getting an error in this line when running the query
Well yes - what did you expect? '1900-01-01 00:00:00' isn't an integer, so you can't add an integer to it.
Think about what you are trying to do: Group data into two hour "slots".
So start by removing the minutes and seconds from the current DATETIME value, to "group" then into one hour slots.
When you have that working, think about how that can be expanded to two hour slots.
One step at a time here!
[edit]
I'll make it easier - I figured it might have a use, so I knocked up a user defined function to remove the minutes and seconds, and submitted it as a tip:
Removing the minutes and seconds from a DATETIME[
^]
Two hour windows are pretty trivial when you have that working
[/edit]