September 6, 2012 at 3:52 am
BEGIN TRAN
DECLARE @HUL_OUTLET_CODE VARCHAR(50)
DECLARE @FGLAT FLOAT
DECLARE @FGLONG FLOAT
DECLARE @FGLAT_P FLOAT
DECLARE @FGLONG_P FLOAT
DECLARE @GPS_DIST FLOAT
--DECLARE @RNO INT
--SET @GPS_DIST<50
DECLARE OUTER_CURSOR CURSOR FOR
select A.HUL_OUTLET_CODE,A.FGLAT,A.FGLONG,B.FGLAT,B.FGLONG from tem1 A JOIN tem2 B
ON A.HUL_OUTLET_CODE=B.HUL_OUTLET_CODE --order by B.HUL_OUTLET_CODE --where @GPS_DIST<=50
OPEN OUTER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @HUL_OUTLET_CODE,@FGLAT, @FGLONG,@FGLAT_P, @FGLONG_P--,@RNO
WHILE @@FETCH_STATUS = 0--@GPS_DIST<=50
Begin
UPDATE tem1 SET GPS_DIST = ABS((geography::Point(@FGLAT,@FGLONG,4326)).STDistance(geography::Point(@FGLAT_P,@FGLONG_P,4326)))
WHERE HUL_OUTLET_CODE=@HUL_OUTLET_CODE
FETCH NEXT FROM OUTER_CURSOR
INTO @HUL_OUTLET_CODE,@FGLAT,@FGLONG,@FGLAT_P,@FGLONG_P--,@RNO
END
CLOSE OUTER_CURSOR
DEALLOCATE OUTER_CURSOR
COMMIT TRAN
---------------------------------------------------------
CREATE TABLE TEM1(HUL_OUTLET_CODE VARCHAR(20),FGLAT FLOAT,FGLONG FLOAT,GPS_DIST FLOAT)
INSERT INTO TEM1 VALUES('HUL-215431D-P017',22.66778190,88.39298473)
INSERT INTO TEM1 VALUES('HUL-215431D-P017',22.66729268,88.39343005)
INSERT INTO TEM1 VALUES('HUL-215431D-P017',22.66735483,88.39348085)
CREATE TABLE TEM2(HUL_OUTLET_CODE VARCHAR(20),FGLAT FLOAT,FGLONG FLOAT,GPS_DIST FLOAT)
INSERT INTO TEM2 VALUES('HUL-215431D-P017',21.66778190,86.39298473)
-------need cursor for to calculate gps_dist based on tem 2 hul_outlet_code here for all tem1 3 values its updating same value .but based on the geography function it has to for every record it has to update different dist . I tried use row_number also but its not working please help on this .
September 6, 2012 at 4:23 am
Try this instead of your cursor: -
UPDATE TEM1
SET GPS_DIST = ABS((GEOGRAPHY::Point(A_FGLAT, A_FGLONG, 4326)).STDistance(GEOGRAPHY::Point(B_FGLAT, B_FGLONG, 4326)))
FROM (SELECT A.HUL_OUTLET_CODE, A.FGLAT, A.FGLONG, B.FGLAT, B.FGLONG
FROM TEM1 A
INNER JOIN TEM2 B ON A.HUL_OUTLET_CODE=B.HUL_OUTLET_CODE
) a(HUL_OUTLET_CODE, A_FGLAT, A_FGLONG, B_FGLAT, B_FGLONG)
WHERE a.HUL_OUTLET_CODE = TEM1.HUL_OUTLET_CODE AND a.A_FGLAT = TEM1.FGLAT AND a.A_FGLONG = TEM1.FGLONG;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply