Dear Mas Bintar, sudah di perbaiki yah, tolong di UPDATE SP nya GETDISTANCE dengan ini
ALTER PROCEDURE [dbo].[GETDISTANCE]
(
@locStarLat FLOAT,
@locStartLong FLOAT,
@Employee varchar (max)
)
AS
--DECLARE @locStarLat FLOAT = -6.3147526
--DECLARE @locStartLong FLOAT = 106.6450365
--DECLARE @MetersPerMile FLOAT = 1000
--DECLARE @Employee varchar (max)='00000952'
declare @empoffice VARCHAR
DECLARE @now VARCHAR
DECLARE @distance FLOAT;
DECLARE @distDiff FLOAT;
DECLARE @unit VARCHAR;
SELECT
@distDiff = Value1,
@unit = Value2
FROM
dbo.PCMEPGENPARAM
WHERE
Parameter = 'WFATTDISTANCE';
SET @now = dbo.fn_formatdatetime(GETDATE, 'yyyymmdd')
set @empoffice =(select top (1) EmployeeOffice from PHRPA0002 pa2 where EmployeeID= @Employee and pa2.StartDate <= @now
AND pa2.EndDate >= @now )
SELECT
TOP @distance = tbl1.JarakKM
FROM
(
SELECT
EmployeeOffice,
EmployeeOfficeDesc,
LintangSelatan,
LintangUtara,
ROUND(
(
6371 * ACOS(
COS) * COS) * COS - RADIANS)
+ SIN) * SIN)
)
),
2
) AS JarakKM,
''WarningType
FROM
dbo.PCMEPEMPOFF
) AS tbl1
WHERE
tbl1.JarakKM IS NOT NULL and EmployeeOffice = @empoffice
ORDER BY
tbl1.JarakKM ASC
--select @unit
IF BEGIN
SET
@distance = @distance * 1000;
END;
SELECT
EmployeeOffice,
EmployeeOfficeDesc,
LintangSelatan,
LintangUtara,
@distDiff/1000 AS JarakKM,
@distDiff AS JarakM,
--ROUND(
-- (
-- 6371 * ACOS(
-- COS) * COS) * COS - RADIANS)
-- + SIN) * SIN)
-- )
-- ),
-- 0
-- ) AS JarakOfficeM,
ROUND(6371000.0 * 2 * ATN2(
SQRT(
SIN / 2) * SIN / 2) +
COS) * COS) *
SIN / 2) * SIN / 2)
),
SQRT(
1 - (
SIN / 2) * SIN / 2) +
COS) * COS) *
SIN / 2) * SIN / 2)
)
)
),0) AS JarakOfficeM,
''WarningType,
'' WarningId,
'2000;4000' AS AnywhereAttendance
FROM
dbo.PCMEPEMPOFF
where EmployeeOffice = @empoffice
