i had plot data from Entry Pass Scan in / out system into Database and write calculation in SQL script to generate Report in Aspx web page.
i had different type of shift pattern following by
EP_SHIFT_NAME EP_SHIFT_DESC EP_SHIFT_TIMEFR EP_SHIFT_TIMETO R1 Off Day 09:00 23:59 W1 W1 08:00 17:00 R2 Rest Day 09:00 23:59 D1 Day Shift 07:00 16:00 NS Normal Shift 08:00 17:30 E1 Day Shift 08:00 17:00 E2 Night Shift 20:00 05:00 N1 Night Shift 19:00 04:00
Currently i had facing a problem which is
a)Total actual working minute(as Work MIn) is 720min per day[12 hours per day] (if more than 720 minutes working hours is consider under category Extra OT hour).
b) Cut off the scan in and out time from 0700 ~ 1900 (DAY) and 1900 ~ 0700(NIGHT) calculation.
However, if the person late in or early out the calculation of the time is base from the last and the first scan.
Anyone can help me or amend the calculation in SQL Script ?
For the Work Hour calculation, i have a idea which is catch the (last scan time - EP_SHIFT_TIMEFROM) for every shift type
Kindly advise , thank you
SELECT CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) AS SCANDATE
,FIRSTSCAN.EP_EMP_COMPANY
,FIRSTSCAN.EP_EMP_DEPT
,FIRSTSCAN.EP_EMP_ID
,FIRSTSCAN.EP_EMP_NAME
,FIRSTSCAN.EP_EMP_SECTION
,FIRSTSCAN.EP_EMP_SHIFT
,FIRSTSCAN.EP_SHIFT
,right(FIRSTSCAN.EP_SCAN_DATE,6) AS FIRSTSCAN
,right(LASTSCAN.EP_SCAN_DATE,6) AS LASTSCAN
,SCANTIMECAL.INFAB AS INFAB_MIN
,SCANTIMECAL.OUTFAB AS OUTFAB_MIN
,DATEDIFF(MI,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_MIN
,DATEDIFF(HOUR,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_HOUR
,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2'
THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1'
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' +
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,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2)
ELSE ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) END AS OTWORK_HOUR
,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1')
AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1'
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' +
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,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 THEN 3 - 0.25 END OTHOUR_FIX
,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1'
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' +
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,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3
THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1'
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' +
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,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME),
CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT) - 3,2) ELSE
CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
,Cast(STUFF(Stuff(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2)END END OTHOUR_EXTRA
,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN FIRSTSCAN.LATEIN END LATEIN
,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN SCANTIMECAL.OUTFAB >= SHIFTDESC.EP_SHIFT_OGRACE THEN 1 END END AS BREAK_ABNORMAL
,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),
CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108),108),':','') THEN 1 END END AS EARLYOUT_NORMAL
,(CASE WHEN FIRSTSCAN.EP_SHIFT<> 'NS' AND FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2'
THEN (CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+3,CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108)),108),':','') THEN 1 END) END)AS EARLYOUT_SHIFT
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY SCANHIST.EP_EMP_ID,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) < '130000' AND SHIFTCAL.EP_SHIFT = 'N1'
THEN CONVERT(VARCHAR(8),DATEADD(DAY,-1,CONVERT(VARCHAR(8),LEFT(SCANHIST.EP_SCAN_DATE,8),112)),112)
ELSE LEFT(SCANHIST.EP_SCAN_DATE,8)
END ORDER BY SCANHIST.EP_EMP_ID) AS RowNum
,SCANHIST.EP_SCAN_DATE
,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) < '130000' AND SHIFTCAL.EP_SHIFT = 'N1'
THEN CONVERT(VARCHAR(8),DATEADD(DAY,-1,CONVERT(VARCHAR(8),LEFT(SCANHIST.EP_SCAN_DATE,8),112)),112)
ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) END AS EMP_WORKDATE
,EMPINFO.EP_EMP_COMPANY
,SCANHIST.EP_EMP_ID
,SCANHIST.EP_EMP_NAME
,SCANHIST.EP_EMP_DEPT
,SCANHIST.EP_EMP_SECTION
,SCANHIST.EP_EMP_SHIFT
,SHIFTCAL.EP_SHIFT
,SCANHIST.EP_SCAN_ID
,SCANHIST.EP_TRANS_LOC
,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) > REPLACE(SHIFTDESC.EP_SHIFT_TIMEFR,':','') THEN 1 END AS LATEIN
FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST
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(SCANHIST.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 >= '20130524' + ' ' +
CASE WHEN (SHIFTCAL.EP_SHIFT <> 'R1' AND SHIFTCAL.EP_SHIFT <> 'R2')
THEN REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-4,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','')
ELSE REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-0,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') END
AND SCANHIST.EP_SCAN_DATE < CASE WHEN (SHIFTCAL.EP_SHIFT = 'N1')
THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,'20130526'),112) + ' ' +
REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE CASE WHEN (SHIFTCAL.EP_SHIFT = 'R1' OR SHIFTCAL.EP_SHIFT = 'R2')
THEN '20130526' + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','')
ELSE '20130526' + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
AND SCANHIST.EP_TRANS_LOC = 'IN'
AND EMPINFO.EP_EMP_LEVEL > '10'
--AND EMPINFO.EP_EMP_LEVEL <> ''
--AND SCANHIST.EP_EMP_DEPT = ''
--AND SCANHIST.EP_EMP_SECTION = ''
--AND SCANHIST.EP_EMP_SHIFT = ''
--AND SCANHIST.EP_EMP_ID = ''
AND SCANHIST.EP_EMP_SHIFT ='A'
)FIRSTSCAN
OUTER APPLY
(
SELECT TOP 1
SCANHIST.EP_SCAN_DATE
,EMPINFO.EP_EMP_COMPANY
,SCANHIST.EP_EMP_ID
,SCANHIST.EP_EMP_NAME
,SCANHIST.EP_EMP_DEPT
,SCANHIST.EP_EMP_SECTION
,SCANHIST.EP_EMP_SHIFT
,SHIFTCAL.EP_SHIFT
,SCANHIST.EP_SCAN_ID
,SCANHIST.EP_TRANS_LOC
FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST
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_TRANS_LOC = 'OUT'
AND EMPINFO.EP_EMP_LEVEL > '10'
AND EMPINFO.EP_EMP_LEVEL <> ''
AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID
ORDER BY SCANHIST.EP_SCAN_DATE )LASTSCAN
OUTER APPLY
(
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
JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
ON SHIFTCAL.EP_SHIFT_NAME = FIRSTSCAN.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 FIRSTSCAN.RowNum = 1
AND CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) BETWEEN '20130524' AND '20130526'
ORDER BY
FIRSTSCAN.EP_EMP_ID
|