Calculation For Time Attendance System

• caulson

  SSC Enthusiast

  Points: 125

  More actions

  July 4, 2013 at 2:28 am

  #395086

  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

  

  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