Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Unanswered: Displaying transaction date and time for shift

    Hi All,

    I want to make a query to retrieve inTime and outTime for
    night shift which starts from 22.00PM to 6.30AM.
    if a employee enters at 22.00 on 01-01-2010 and goes back at 6.30 on
    02-01-2010.
    But the output should be
    Date - 01-01-2010
    inTime-22.00PM
    outTime-6.30AM

    The actual data stores like this

    EmpCode Date TransactionTime
    011 01-01-2010 22.00PM
    011 02-01-2010 6.30AM
    011 02-01-2010 21.59PM
    011 03-01-2010 6.45AM
    011 03-01-2010 22.00PM

    I want the output like this

    Empcode Date InTime outTime
    011 01-01-2010 22.00PM 6.30AM
    011 02-01-2010 21.59PM 6.45AM
    011 03-01-2010 22.00PM -

    Can anybody help me.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How do you know that the record at 01-01-2010 22.00PM is "in", and not "out"?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2007
    Posts
    38
    Hi

    Try this.
    We know that the employee is on night shift and we can determine which one is IN and which one is OUT. Based on this assumption, tweak the data a little bit by adding -12 hours to the entered data. Then you can see the employee's work hours in the same date.

    if object_id('tempdb..#trans_tbl') is not null
    drop table #trans_tbl

    CREATE TABLE #trans_tbl(ID INT identity(1,1), EmpCode INT, TransactionDateTime DateTime)
    INSERT INTO #trans_tbl Values(011, '2010/01/01 22:00:00')
    INSERT INTO #trans_tbl Values(011, '2010/01/02 06:30:00')
    INSERT INTO #trans_tbl Values(011, '2010/01/02 21:59:00')
    INSERT INTO #trans_tbl Values(011, '2010/01/03 06:45:00')
    INSERT INTO #trans_tbl Values(011, '2010/01/03 22:00:00')

    -- tweak the data by adding -12 hours
    Update #trans_tbl
    Set TransactionDateTime=dateadd(hour,-12,TransactionDateTime)

    -- then add 12 hours when display actual time
    SELECT EmpCode, TransDate
    , Convert(varchar(10),DateAdd(Hour,12,Cast(TransDate + ' ' + TransStartTime as datetime)), 108) TransStartTime
    , Case when TransEndTime Is Null Then '-'
    else Convert(varchar(10),DateAdd(Hour,12,Cast(TransDate + ' ' + TransEndTime as datetime)), 108)
    end TransEndTime
    FROM (
    SELECT
    EmpCode
    ,convert(varchar(10), TransactionDateTime, 111) TransDate
    ,Min(convert(varchar(10), TransactionDateTime, 108)) TransStartTime
    ,Case when Max(convert(varchar(10), TransactionDateTime, 108))
    =Min(convert(varchar(10), TransactionDateTime, 108)) then NULL
    else Max(convert(varchar(10), TransactionDateTime, 108)) end
    TransEndTime
    FROM #trans_tbl
    Group By EmpCode, convert(varchar(10), TransactionDateTime, 111)
    ) A

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •