Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Strange group by day problem

Strange group by day problem

Strange group by day problem

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.

RE: Strange group by day problem

Please post your SQL.

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

RE: Strange group by day problem

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):
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,
      from tab)
group by xdate

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:
select xdate, sum(value)
from (select cast(tscolumn - interval'5' hour as date) as xdate, value
      from tab)
group by xdate

BTW, Date and Time are both reserved words by ANSI SQL. (See list here http://developer.mimer.com/validator/sql-reserved-words.tml)

RE: Strange group by day problem

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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close