I have two tables like this:
Table Experiment (I will just use one roadID (rID) for now)
rID | latBegin |longBegin |latEnd |longEnd
041 | 23.875464|-80.456798|30.549879|-83.5465521
041 | 33.776563|-81.157526|33.248261|-81.2468425
For each of the above rows I want the cursor to get the rID, latBegin,longBegin and check the distance from the lat and long in
Table ExperimentDetails
rID | lat |long |temp |direction|tension
041 | 33.665632|-81.137952|56.23|L |54.54
041 | 23.245632|-80.127952|56.23|L |23.54
041 | 23.454555|-80.232456|66.23|L |13.54
041 | 23.568763|-80.346586|26.23|L |63.54
041 | 23.796545|-80.446586|86.23|L |83.54
041 | 23.996545|-80.946586|96.23|L |22.54
The steps for the query are:
Step 1: Get latBegin and longBegin for a specific rID from Table Experiment
Step 2: Go to ExperimentDetails and run this calculation to get the distance difference for each lat and long in the Details table
Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000
Step 3: Get the minimum value for this new associated column in Details Table
Step 4: Get the associated temp,direction, and tension values for that minimum value and place it in a new table (Not done yet> Help needed)
Step 5: Get the average of temp, tension for the previous 30 feet or 360 inches from the min value and place it in another table (columns: rID, latBegin,longBegin,avgTension,avgtemp Not done yet>Help needed)
This is my code so far:
DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10)
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT rid,latbeg,longbeg
FROM dbo.Experiment
WHERE rID='041'
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)
, @temp=temp
FROM dbo.ExperimentDetails
WHERE rid='041'
Insert into dbo.Test(rid,lat,lon,min,temp) values (@rid,@latBegin,@longBegin,@minV,@temp)
FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
The Test table looks like this:
rID|lat |long |min |temp
041|23.875464|-80.456798|12.1|96.23
041|33.776563|-81.157526|11.0|96.23
The temp data is repeated for the last row, which is not correct
It should look like this:
rID|lat |long |min |temp
041|23.875464|-80.456798|12.1|83.54
041|33.776563|-81.157526|11.0|54.54
I have given as much information as a I could, but if you need more please ask, would appreciate any help, been banging my head on this since yesterday. Currently I'm trying to bring in temp, direction, and tension in addition to @minV, but it does not let me do it
For Step 5:
The calculation in the cursor looks like this in theory for Details table:
rID |lat |long |temp |DISTDIFF
041 | 23.245632|-80.127952|56.23|372.12
041 | 23.454555|-80.232456|66.23|300.22
041 | 23.568763|-80.346586|26.23|50.48
041 | 23.796545|-80.446586|86.23|12.10
Then when inserting I check the minimum value i.e. 12.10 and insert the associated values with that row.
For step 5, I need to find the value closest to 360 BEFORE the minimum value not after (there might be other values closer after), which in this case is 372.12 and then take the average of temp like this:
rID |minlat |minlong |lastlat |lastlong |Avgtemp
041 | 23.796545|-80.446586|23.245632|-80.127952|58.73
Using Sql Server 2014
Best Answer
I think I have a solution for you... but!!! IMPORTANT: You are not considering having more than 1 record in DETAIL that equals the Min Value... You also are missing the TENSION column in TEST table
Anyway, to answer your question try replacing this:
With this:
Good Luck!
---FOR STEP 5 -----
First of all, I will assume that you already have created the other table let's say: Temp360
So, include another 2 variables in the DECLARE zone... let's say: @min360 decimal(15,10) and @avgtemp decimal(15,10)
then use this code inside your cursor loop: