Sorry, I can't think of how to title what I'm trying to do - same reason I can't find a solution and am asking here.
I have a insert into...select statement that is performing a math calculation - but it could potentially frequently be calling it hundreds of thousands of times - or even into the millions of times. So I need a way to adjust the resulting value - preferably without doing the whole calculation again for the minor adjustment.
Here is the select statement as is:
declare @NewLocationID int
declare @iLatFrom decimal(18,8)
declare @iLongFrom decimal(18,8)
select @NewLocationID = inserted.ID,
@iLatFrom = inserted.Latitude,
@iLongFrom = inserted.Longitude
from inserted
insert into juncLocationLocation
select @NewLocationID, L.ID,
(DEGREES(
ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))))
as [Bearing]
from [Location] L where L.ID <> @NewLocationID
The equation above results in a negative value if the Bearing is more than 180°. So I want to add 360 to the result whenever the result is negative, but I don't want to do something like:
insert into juncLocationLocation
select @NewLocationID, L.ID,
(case when DEGREES(
ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))) < 0
then
DEGREES(
ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))) + 360
else
DEGREES(
ATN2(SIN(RADIANS(L.Longitude) - RADIANS(@iLongFrom)) *
COS(RADIANS(L.Latitude)), (COS(RADIANS(@iLatFrom)) *
SIN(RADIANS(L.Latitude))) - (SIN(RADIANS(@iLatFrom)) *
COS(RADIANS(L.Latitude)) * COS(RADIANS(@iLongFrom) - RADIANS(L.Longitude))))))
as [Bearing]
from [Location] L where L.ID <> @NewLocationID
As this would result in the equation being called twice - once for the negative value check and then to get the then/else result into the select statement as Bearing.
Is there some way to calculate the result - and then based on the answer to just add 360 to the result (if negative) and then return the modified result - so this equation is only done once?
I'm trying to improve the speed at which this process is done - and thus not wanting to calculate this twice on every insert iteration
Thanks!