SQL Server Performance

Problem in query

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Sep 18, 2013.

  1. immad Member

    Hello,

    i have a problem regarding query,

    my data is like this

    EID---------checktime--------------status-----
    123-----7/1/2013 08:00:00 PM-------I---------
    123-----7/2/2013 02:00:00 AM------O---------

    I means in and O means Out

    employee enter in a factory on 7/1/2013 08:00:00 PM
    and out in a factory on 7/2/2013 02:00:00 AM at night becouse after 12:00 date is change.

    i want a query that shows data like this

    EID----------checktime----------------status-----
    123-----7/1/2013 08:00:00 PM---------I---------
    123-----7/1/2013 02:00:00 AM--------O---------

    Please Help me out.
    Thanks
  2. Shehap MVP, MCTS, MCITP SQL Server

    Please try the below query and let me know your feedback

    Code:
    create table #table (EID int , checktime datetime ,status varchar (10))
    
    insert into #table select 123,'2013-07-01 08:00:53 ','I' union select 123,'2013-07-02 02:00:53 ','O'
    ;
    with temp as
    
    (SELECT ROW_NUMBER () OVER ( partition by EID , DATEDIFF (D,CHECKTIME , GETDATE()) ORDER BY EID) RANKO, * FROM #table)
    
    UPDATE TEMP SET checktime=checktime-1 WHERE RANKO=1 AND DATEPART(hour,checktime) <8 and        checktime like '%am'
    
    select * from #table
    
    DROP TABLE #table
     
    

Share This Page