SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Maxs conditional on other columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jtupper
Starting Member

1 Posts

Posted - 06/28/2010 :  12:37:53  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 06/29/2010 :  16:29:48  Show Profile  Reply with Quote
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

USA
68 Posts

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

Pete
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000