Strange group by day problem 1

[st99015207]

Hi all,

We have an application that uses a standard 24 hours period for a reporting 'day', but the day doesnt run from midnight to midnight, it actually runs from 5AM to 5AM.

Its easy to return aggregate totals grouped by the day using datepart for a standard mignight to midnight day, but how would I acheive the same grouping using my strange 5AM-> 5AM period??

The table in question has a column for the date and a seperate column for the time.

Example data from table:

Date Time Value
---------------------------------------
01/01/2009 15:00:00 23.40
01/01/2009 21:00:00 40.50
01/01/2009 02:00:00 60.00
02/01/2009 05:10:00 30.45
03/01/2009 01:12:00 30.00
05/01/2009 15:00:00 24.60

Desired Output:

Date Total
----------------------
01/01/2009 123.90
02/01/2009 60.45
05/01/2009 24.60

I hope this makes sense?!
I already have the where clause for returning rows that fall between two dates/times....Therefore its simply the grouping logic I am struggling with...

Many thanks in adavance.
Nick

 

[genomon]

Please post your SQL.

Cogito eggo sum – I think, therefore I am a waffle.

 

[JarlH]

Storing date and time in different columns is probably the reason why this qyery is a bit tricky. To find the desired date some date/time arithmetics has to be done (untested):
[tt]select xdate, sum(value)
from (select cast((cast(datecol as timestamp)
+ (timecol- time'00:00:00') hour to second
- interval '5' hour) as date) as xday,
value
from tab)
group by xdate[/tt]

xdate is the adjusted date, i.e. the original date + the original time - 5 hours.

If you had stored the date and time as one single timestamp column the query had been like:
[tt]select xdate, sum(value)
from (select cast(tscolumn - interval'5' hour as date) as xdate, value
from tab)
group by xdate
[/tt]

BTW, Date and Time are both reserved words by ANSI SQL. (See list here

http://developer.mimer.com/validator/sql-reserved-words.tml)

 

[JarlH]

Oops, to fast (again).

You probably want to add those 5 hours to xdate in the select list, e.g. select cast(xdate + interval '5' hour...