# Getting two or more column values through a Cursor SQL Server

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
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

• 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? – Aaron Bertrand Jul 12 '18 at 13:34
• @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 – SQLserving Jul 12 '18 at 13:37
• 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... – Aaron Bertrand Jul 12 '18 at 13:44
• @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. – SQLserving Jul 12 '18 at 14:15

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

``````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
``````
• Everything before step 1 worked for me! Did not use the solution for Step 5 as it was not what I was looking for. – SQLserving Jul 17 '18 at 13:17