SQL Server Attendance Time In Time Out Issue

Question

I am making time in time out attendance system. Example table i have i am just trying to set the value in first right in time and very right time out then i have calculated easily other entries are dummy i tried a lot but no help please see and suggest of

Actual data

Id      Pin     Time                    DeviceMode
---------------------------------------------------
23086   7756    2023-01-06 07:27:00.000 IN
23237   7756    2023-01-06 09:10:00.000 OUT
23241   7756    2023-01-06 09:11:00.000 OUT
23246   7756    2023-01-06 09:12:00.000 OUT
23248   7756    2023-01-06 09:13:00.000 OUT
23301   7756    2023-01-06 10:20:00.000 OUT

I want to make this

Id      Pin     Time In                     DeviceMode     Time Out                 
---------------------------------------------------------------------------------------
23086   7756    2023-01-06 07:27:00.000         IN         2023-01-06 10:20:00.000  OUT
23237   7756    2023-01-06 09:10:00.000         OUT
23241   7756    2023-01-06 09:11:00.000         OUT
23246   7756    2023-01-06 09:12:00.000         OUT
23248   7756    2023-01-06 09:13:00.000         OUT
23301   7756

from (
select  *, 
            in_mins = CASE  
            WHEN    DeviceMode in ('IN')
            AND     LEAD(DeviceMode) OVER (PARTITION BY Pin ORDER BY [Time]) in ('OUT')
            THEN    LEAD([Time]) OVER (PARTITION BY Pin ORDER BY [Time] )
                            ELSE    0
                            END
             --out_mins= CASE  WHEN    DeviceMode in ('OUT')
    --                        AND     LEAD(DeviceMode) OVER (PARTITION BY Pin ORDER BY [Time]) in ('OUT')
    --                        THEN    DATEDIFF(MINUTE, 
    --                                         [Time], 
    --                                         LEAD([Time]) OVER (PARTITION BY Pin ORDER BY [Time]))
    --                        ELSE    0
    --                        END
    from    EmpLogs2
    ) t 
    order by time

score 0 · Answer 1 · answered Jun 03 '23 at 22:44

I first found biggest Time of each DeviceMode and then calculated the row number And If DeviceMode "in" and from the row number is one( so that if I have several "in", first has a select) show biggest Time


select 
        Id
        ,Pin
        ,Time
        ,DeviceMode
        ,case 
              when DeviceMode= 'IN' and rw=1 then 
                LEAD(lastout) OVER (  order by (select 0))
              else null 
              end

from (
            select *,last_Value(Time)  OVER (PARTITION BY Pin order by DeviceMode)   as lastout
            ,row_number()  OVER (PARTITION BY Pin,DeviceMode order by DeviceMode)   as rw
            from EmpLogs2
)d

Demo

SQL Server Attendance Time In Time Out Issue

1 Answers1