×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Strange group by day problem

## Strange group by day problem

(OP)
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...

Nick

### RE: Strange group by day problem

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!