I am using following SQL query to find difference between two datetime fields which is calculate night time shift,
For example
today work time is evening 190957 til next date morning 071152 so the between time in fab minute is 628, but out fab time will be calculate 798 min(Over calculate, more than IN fab time). so i want calculate in accurate minute.
SELECT
GROUP_SCANTIMECAL.EP_EMP_ID
,SUM(CAST(GROUP_SCANTIMECAL.INFAB_MIN AS FLOAT)) AS INFAB
,SUM(CAST(GROUP_SCANTIMECAL.OUTFAB_MIN AS FLOAT)) AS OUTFAB
FROM
(
SELECT SCANHIST.EP_SCAN_DATE
,SCANHIST.EP_EMP_ID
,SCANHIST.EP_EMP_NAME
,SCANHIST.EP_EMP_DEPT
,SCANHIST.EP_EMP_SECTION
,SCANHIST.EP_EMP_SHIFT
,SCANHIST.EP_TRANS_LOC
,DATEDIFF(MI,CAST(STUFF(STUFF(SCANHIST.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME),
CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS INFAB_MIN
,DATEDIFF(MI,CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME),
CAST(STUFF(STUFF(PREVSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS OUTFAB_MIN
FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST
OUTER APPLY
(
SELECT TOP 1
NEXTSCAN.EP_SCAN_DATE
,NEXTSCAN.EP_EMP_ID
,NEXTSCAN.EP_EMP_NAME
,NEXTSCAN.EP_EMP_DEPT
,NEXTSCAN.EP_EMP_SECTION
,NEXTSCAN.EP_EMP_SHIFT
,NEXTSCAN.EP_SCAN_ID
,NEXTSCAN.EP_TRANS_DESC
,NEXTSCAN.EP_TRANS_LOC
FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] NEXTSCAN
JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
ON EMPINFO.EP_EMP_ID = NEXTSCAN.EP_EMP_ID
JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
ON SHIFTCAL.EP_SHIFT_NAME = NEXTSCAN.EP_EMP_SHIFT
AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
WHERE 1=1
AND SCANHIST.EP_SCAN_ID = NEXTSCAN.EP_SCAN_ID
AND NEXTSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE
AND NEXTSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1')
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(NEXTSCAN.EP_SCAN_DATE,8)),112) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2')
THEN
LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE
LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
AND NEXTSCAN.EP_TRANS_LOC = 'OUT'
ORDER BY NEXTSCAN.EP_SCAN_DATE
)NEXTSCAN
OUTER APPLY
(
SELECT TOP 1
PREVSCAN.EP_SCAN_DATE
,PREVSCAN.EP_EMP_ID
,PREVSCAN.EP_EMP_NAME
,PREVSCAN.EP_EMP_DEPT
,PREVSCAN.EP_EMP_SECTION
,PREVSCAN.EP_EMP_SHIFT
,PREVSCAN.EP_SCAN_ID
,PREVSCAN.EP_TRANS_DESC
,PREVSCAN.EP_TRANS_LOC
FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] PREVSCAN
JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
ON EMPINFO.EP_EMP_ID = PREVSCAN.EP_EMP_ID
JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
ON SHIFTCAL.EP_SHIFT_NAME = PREVSCAN.EP_EMP_SHIFT
AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
WHERE 1=1
AND SCANHIST.EP_SCAN_ID = PREVSCAN.EP_SCAN_ID
AND PREVSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE
AND PREVSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1')
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(SCANHIST.EP_SCAN_DATE,8)),112) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2')
THEN
LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE
LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
AND PREVSCAN.EP_TRANS_LOC = 'IN'
ORDER BY PREVSCAN.EP_SCAN_DATE
)PREVSCAN
JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID
JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
WHERE 1=1
AND SCANHIST.EP_SCAN_DATE >= FIRSTSCAN.EP_SCAN_DATE
AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1')
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE
CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2')
THEN
LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE
LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID
AND SCANHIST.EP_TRANS_LOC = 'IN'
)GROUP_SCANTIMECAL
GROUP BY GROUP_SCANTIMECAL.EP_EMP_ID
)SCANTIMECAL
