Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Maxs conditional on other columns

Author  Topic 

jtupper
Starting Member

1 Post

Posted - 2010-06-28 : 12:37:53
I have data structured as follows: column1 = datetime, column2=int

I am needing to take the max int from each hour and subtract the max int from the previous hour. i have already broken the datetime in to the datepart but am struggling to get the previous max. The problem is that there is not data for every hour but if the previous hour is null I am needing to go to the next previous hour until there is data. Any ideas of how to structure this?

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 16:29:48
You could select the date without the hour, the hour and the int
which don't have nulls, sort it by date and hour into a a table with an identity field.

then join that table to itself via the identity field referencing the identity before it to get the previous max int, then you can subtract the two ints from each other.

If you post some example data, I can write a query for you.
Pete
Go to Top of Page

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 16:31:22
That should be:
"You could select the date without the hour, the hour and the max int"

Pete
Go to Top of Page
   

- Advertisement -