Home Forums SQL Server 7,2000 T-SQL Time Keeping RE: Time Keeping

  • January 11, 2016 at 4:11 am

    #1850909

    Well, Following is the solution after the understanding i get from your answers.

    Please note that i have shared you SQL SERVER 2000 solution as this question is in SQL Server 7,2000.

    1st created a variable table to get the row id.

    Declare @Transaction Table

    (

    RowIDint identity(1,1),

    EmployeeCodevarchar(5),

    TransactionDate datetime,

    EventDateTimedatetime,

    EventTypevarchar(3)

    )

    2nd data prep.

    --- Expended sample data, because after spending 24 i have asked the person to come again after an hour :D

    insert into @Transaction

    SELECT a.EmployeeCode

    , Convert(datetime, convert(nvarchar(10),a.TransactionDate, 101)) AS TransactionDate

    , a.TransactionDate

    , a.TransactionType

    FROM

    (

    --------------- Day One

    Select '00445' as EmployeeCode, '2014-08-02 06:43:09.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 09:43:41.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 09:56:26.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 12:35:44.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 13:45:56.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 20:40:55.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 21:20:30.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 05:30:18.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    -------------- Day Two

    Select '00445' as EmployeeCode, '2014-08-03 06:43:09.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 09:43:41.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 09:56:26.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 12:30:44.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 13:50:56.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 20:40:55.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 23:20:30.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 07:30:18.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    -------------- Day Three

    Select '00445' as EmployeeCode, '2014-08-04 07:43:09.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 09:43:41.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 09:56:26.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 12:15:44.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 13:45:56.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 20:40:55.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 23:20:30.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 23:59:18.000' as TransactionDate, 'OUT'as TransactionType

    ) A

    Order by A.EmployeeCode, A.TransactionDate

    Now the original problem was, if you have a Checkin of a person and its last check out comes in the next day, it would would have be considered at that date. so this will get you your desired result. i have extended your sample data which two difference scenarios.

    Following is the code

    SELECTM.EmployeeCode

    , adj_TransactionType

    , CASE

    WHEN adj_TransactionType = 'IN'THEN MIN(adj_EventDateTime)

    WHEN adj_TransactionType = 'Lunch OUT' THEN MIN(adj_EventDateTime)

    WHEN adj_TransactionType = 'Lunch IN'THEN MAX(adj_EventDateTime)

    WHEN adj_TransactionType = 'OUT'THEN MAX(adj_EventDateTime)

    END AS calcEventDateTime

    FROM

    (

    ---- use self join to get the next row

    SELECT

    t.EmployeeCode

    , t.TransactionDate

    , CASE

    WHEN DATEPART(hh, t.EventDateTime) IN (12,13) THEN 'Lunch ' + t.EventType

    ELSE

    (CASE WHEN a.max_RowId IS NOT NULL THEN 'OUT' ELSE t.EventType END )

    END adj_TransactionType

    , CASE WHEN a.max_RowId IS NOT NULL THEN t1.EventDateTime ELSE t.EventDateTime END as adj_EventDateTime

    FROM

    @Transaction t

    LEFT JOIN @Transaction t1on t.EmployeeCode = t1.EmployeeCode

    and t.RowID + 1 = t1.RowID

    -------------- This query to get the last element of the chain

    LEFT JOIN

    (

    SELECT

    t.EmployeeCode

    , TransactionDate

    , MAX(RowId) AS max_RowId

    FROM

    @Transaction t

    GROUP BY

    t.EmployeeCode, TransactionDate

    ) A on t.EmployeeCode= a.EmployeeCode

    and t.RowID= a.max_RowId

    ) M

    GROUP BY

    M.EmployeeCode, adj_TransactionType, m.TransactionDate

    ORDER BY

    M.EmployeeCode, m.TransactionDate

    Do test it against real data you have and if the data volume is high replace the Variable table with Temp Table.

    Hope it helps.