July 22, 2013 at 10:04 am
[Edited to drastically simplify question] Please see attached. Thanks!
July 22, 2013 at 10:07 am
Without the details that you can't get to post you are right, this is pretty hard to figure out what is going on. I know that some people do have trouble posting details for some reason. Can you make your ddl and sample data into a file and post it as an attachment?
The one question is what defines previous row? You need to figure out what column(s) define the order.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 10:19 am
This looks a lot like a running total issue. read the following article and come back if you still need help.
July 22, 2013 at 10:32 am
[Please see updated question]
July 22, 2013 at 10:42 am
@luis: Thanks so much for that link: I had read once through that before posting - it does seem the answer is in there, but I'm not seeing it through the fog yet: My version of the problem is strange (to me), it's not a running total as much as a running min and running max which have their own special logic as the numbers are degrees.
But it could be that I'm just focusing on the wrong things.
I'll keep reading through this and other examples. Thanks again!
July 22, 2013 at 10:49 am
Here is the OP's "original post".
--------------------------------------------------------------------
Help! 😉 I have to run through a table of data a couple times (I think) to get the results I need. The subject matter is a bit daunting, so I'm getting stuck looking at the forest instead of the trees. Can you help me figure out a way through?
The table tracks wind direction (polar degrees) for different sites. Each time I do this, I need to loop through the last 15 minutes (aka blocks) of results and based on each site, grab the minimum and maximum degrees (relative to that site on a compass) over that period.
It's "easy" in Excel with the formulas in place, and I'm overwhelmed with how to handle it in SQL. I know I need to run some temp table assessments based on each previous row, but I think I'm so wrapped up in the polar degrees part, I'm not able to think about the "SQL value from previous row" part. If anyone can help me get to a next step, I'd be grateful!
I'll start with example data (on SQL 2008R2):
CREATE TABLE [dbo].[SampleData](
[Location] [varchar](200) NOT NULL,
[Date] [datetime] NOT NULL,
[Direction] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Location] ASC,
[Date] ASC
)
) ON [PRIMARY]
GO
INSERT INTO SampleData Values ('site1','07/22/13 11:30:45',302)
INSERT INTO SampleData Values ('site1','07/22/13 11:31:45',322)
INSERT INTO SampleData Values ('site1','07/22/13 11:32:45',9)
INSERT INTO SampleData Values ('site1','07/22/13 11:33:45',9)
INSERT INTO SampleData Values ('site1','07/22/13 11:34:45',0)
INSERT INTO SampleData Values ('site1','07/22/13 11:35:45',47)
INSERT INTO SampleData Values ('site1','07/22/13 11:36:45',34)
INSERT INTO SampleData Values ('site1','07/22/13 11:37:45',19)
INSERT INTO SampleData Values ('site1','07/22/13 11:38:45',35)
INSERT INTO SampleData Values ('site1','07/22/13 11:39:45',37)
INSERT INTO SampleData Values ('site1','07/22/13 11:40:45',37)
INSERT INTO SampleData Values ('site1','07/22/13 11:41:45',350)
INSERT INTO SampleData Values ('site1','07/22/13 11:42:45',26)
INSERT INTO SampleData Values ('site1','07/22/13 11:43:45',26)
INSERT INTO SampleData Values ('site1','07/22/13 11:44:45',26)
INSERT INTO SampleData Values ('site1','07/22/13 11:45:45',26)
INSERT INTO SampleData Values ('site2','07/22/13 11:30:59',326)
INSERT INTO SampleData Values ('site2','07/22/13 11:31:59',326)
INSERT INTO SampleData Values ('site2','07/22/13 11:32:59',2)
INSERT INTO SampleData Values ('site2','07/22/13 11:33:59',2)
INSERT INTO SampleData Values ('site2','07/22/13 11:34:59',2)
INSERT INTO SampleData Values ('site2','07/22/13 11:35:59',2)
INSERT INTO SampleData Values ('site2','07/22/13 11:36:59',21)
INSERT INTO SampleData Values ('site2','07/22/13 11:37:59',41)
INSERT INTO SampleData Values ('site2','07/22/13 11:38:59',41)
INSERT INTO SampleData Values ('site2','07/22/13 11:39:59',41)
INSERT INTO SampleData Values ('site2','07/22/13 11:40:59',31)
INSERT INTO SampleData Values ('site2','07/22/13 11:41:59',6)
INSERT INTO SampleData Values ('site2','07/22/13 11:42:59',6)
INSERT INTO SampleData Values ('site2','07/22/13 11:43:59',6)
INSERT INTO SampleData Values ('site2','07/22/13 11:44:59',11)
INSERT INTO SampleData Values ('site2','07/22/13 11:45:59',11)
SELECT * from SampleData
So we now have an example of the type of data I need to work with: a table with location/date pairings, and wind direction (polar degrees). Location+Date is unique.
Looking at site1 over time you notice it starts at 302 on the compass (remember 360 = 0, 4 quadrants, etc...) and over 15 minutes it sways up and down the compass as high as 47, and as low as 302 (I use "high" and "low" relatively here): These are my "max" (47) and "min" (302) values - I want to come up with a final result that looks like this:
LOCATION DATE MinDirection MaxDirection
site1 07/22/13 11:45:45 302 47
site2 07/22/13 11:45:59 326 41
WHERE I'M STUCK:
To determine these results, I need to make a temp field "AdjustedDirection" - This value is a conversion of the current angle to allow values over 360 / under zero since the compass resets at zero. By normalizing the value, we can test whether it is truly a (relative) min or max for the set.
I know I'll need a temp table of the data, *vaguely* like this:
CREATE TABLE #temp (
[Location] [varchar](200) NOT NULL,
[Date] [datetime] NOT NULL,
[Direction] [int] NULL,
[AdjustedDirection] [int] NULL,
[RunningMin] [int] NULL,
[RunningMax] [int] )
Then I have to determine the adjusted direction based on the following:
Previous [AdjustedDirection] plus one of the following:
a) IF ABS(Direction - previous Direction) < 180:
Direction - previous direction
b) IF Direction < previous Direction:
360-ABS(Direction - previous Direction))
c) any other case:
-360+ABS(Direction - previous Direction)
AND FINAL STEP:
With a temp table that has [AdjustedDirection] for each location/date, all I need to do is
SELECT
Location,
MAX(Date) as Date,
[MinDirection] = MIN([AdjustedDirection])-INT(MIN([AdjustedDirection])/360)*360
[MaxDirection] = MAX([AdjustedDirection])-INT(([AdjustedDirection])/360)*360
GROUP BY Location
I'm so close (I think) - I just need that middle step - can someone help me figure out how to loop through and get the AdjustedDirection values in a temp table?
Thanks!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 10:51 am
Sean: THANK YOU for the re-format!!!! 😉
July 22, 2013 at 11:25 am
[Please see updated question]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply