We have a SQL Server database that records the date and time of when someone swipes their ID card at a number of different card readers, as well other information such as the person's ID number, the number and name of the card swipe (location), etc. Each swipe is a separate transaction on its own row. I've been asked to create a report (ideally in SSRS) that calculates the difference between when someone punches in and when the punch out - essentially when they punch at one of two card readers, and punch out at one of two card readers. The relevant data in the database is similar to this:

The query that I have to pull the relevant data is:

SELECT TRANSACK_ID, CAST(TRANSACK.TR_DATETIMELOCAL AS datetime) AS Time, TR_SLA, TR_DEV_NAME, TR_MASTER_ID
FROM TRANSACK
where (TRANSACK.TR_SLA = '03010301' OR TRANSACK.TR_SLA = '03010302' OR TRANSACK.TR_SLA = '03010303' OR TRANSACK.TR_SLA = '03010304')
GROUP by TR_MASTER_ID, TRANSACK_ID, TR_DATETIMELOCAL, TR_COMP_NAME, TR_DEV_NAME, TR_SLA
order by TR_MASTER_ID

I can use this query to populate a dataset in SSRS. What I can't figure out is how to calculate the difference in time between each Gate 1 (In) row and Gate1 (Out) row, per each ID in the TR_MASTER_ID column. I've tried all the datediff expressions that I can think of in SSRS without any success, but am probably missing something obvious.

Any suggestions or input would be appreciated.

CodePudding user response：

I added PunchStatus to express if the employee punched correctly. I also evaluated first punch of 'in' and last punch of 'out' as the punches, in order to prevent multiple punches. You can change that part if you want. You can try the following and see how many hours elapsed between punches for an employee in a day:

SELECT TR_MASTER_ID,
        PunchDate
        CASE 
           WHEN FirstPunchTime IS NULL THEN 'No Punch In'
           WHEN LastPunchTime IS NULL THEN 'No Punch Out'
           WHEN FirstPunchTime IS NOT NULL AND LastPunchTime IS NOT NULL THEN 'Success'
        END AS PunchStatus,
        CASE 
           WHEN FirstPunchTime IS NOT NULL AND LastPunchTime IS NOT NULL THEN 
                DATEDIFF(second, FirstPunchTime, LastPunchTime) / 3600.0 END AS TimeElapsedInHours
FROM
(SELECT TR_MASTER_ID,
        CAST(CAST(TRANSACK.TR_DATETIMELOCAL AS datetime) AS date) AS PunchDate,
        MIN(CASE WHEN TR_DEV_NAME LIKE '%(In)%' THEN CAST(TRANSACK.TR_DATETIMELOCAL AS datetime) END AS PunchInTime) AS FirstPunchTime,
        MAX(CASE WHEN TR_DEV_NAME LIKE '%(Out)%' THEN CAST(TRANSACK.TR_DATETIMELOCAL AS datetime) END AS PunchInTime) AS LastPunchTime
FROM TRANSACK
GROUP BY TR_MASTER_ID,
        CAST(CAST(TRANSACK.TR_DATETIMELOCAL AS datetime) AS date)) AS T

Expected output:

CodePudding user response：

This is what we used to accomplish our goal:

cast( (cast(TR_DATETIMELOCAL as datetime) - lag(TR_DATETIMELOCAL) over (order by TR_MASTER_ID, TR_DATETIMELOCAL)) as time) as diff from TRANSACK

The statement was found here: Calculate Time Difference Between Two Consecutive Rows