Getting two or more column values through a Cursor SQL Server

Question

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

• Aaron Bertrand: How exactly have you been trying to bring in temp, direction, and tension, and what does "does not let me do it" mean? I didn't read the whole narrative but are you sure this even needs to be a cursor?
• SQLserving: @AaronBertrand I have not been able to do that. That is where I need help. Not entirely sure, but I couldnt think of any other way, since I have to run each row from table A through all rows in Table B. If I add additional fields to the SELECT portion, it duplicates the last row of Table B for temp,direction, and tension
• Aaron Bertrand: Did you also add variables to both FETCH NEXT commands? I mean, this is really hard to solve, you're describing what you've done in English instead of showing us the code...
• SQLserving: @AaronBertrand No because my cursor is going through the first table only. Now that you mentioned it, would it work if a nested cursor for the second table as well? Never done a nested second cursor.

Answer 1

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:

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) 

With this:

 SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID) 
 FROM dbo.ExperimentDetails WHERE rid=@rid 
 Insert into dbo.Test(rid,lat,lon,min,temp,tension) 
 select rid, @latBegin,@longBegin,@minV,temp,tension from 
 dbo.ExperimentDetails WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @minV 

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)

DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10), @min360 decimal(15,10),@avgtemp decimal(15,10)

then use this code inside your cursor loop:

//part 1... what we already know + JUST getting the AvgTemp 
 SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude), @avgtemp = AVG(temp)
 FROM dbo.ExperimentDetails WHERE rid=@rid 
 Insert into dbo.Test(rid,lat,lon,min,temp,tension) 
 select rid, @latBegin,@longBegin,@minV,temp,tension from 
 dbo.ExperimentDetails WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @minV 
//part 2... something similar + the Average Temp we already got.
 SELECT @min360=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID) 
 FROM dbo.ExperimentDetails WHERE rid=@rid and 
Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) >=360
 Insert into dbo.Test360(rid,lat,lon,min,avgtemp,tension) 
 select rid, @latBegin,@longBegin,@min360,@avgtemp,tension from 
 dbo.ExperimentDetails WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @min360 

• SQLserving: Everything before step 1 worked for me! Did not use the solution for Step 5 as it was not what I was looking for.