Calculation For Time Attendance System

caulsonchua

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(VA

Mycroft Holmes

You are seriously hoping someone will go through that heap of... good luck with that. Try breaking it down to smaller bits that you can work over in isolation and then put the bits together to solve the issue or at least isolate the problem. Loading in a dump like that is not going to get you a positive response.

Never underestimate the power of human stupidity RAH

Amol_B

What you have tried so far?

caulsonchua

I had try out is IN FAB time is out time - In time and Out FAB time is In time - Out time. the problem is now dont know how to apply the logic in my SQL script. kindly advise, thank you