SQL Server Performance

DATA PROBLEM

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Jun 11, 2013.

  1. immad Member

    sir this query give me first time in and last time out

    SELECT a.eid, a.date, d.InTime, e.OutTime
    FROM
    (SELECT DISTINCT eid, date FROM atend) a
    OUTER APPLY (SELECT b.eid, b.Date, MIN(b.Time) InTime FROM atend b WHERE a.eid = b.eid AND a.date = b.date GROUP BY b.eid, b.Date) d
    OUTER APPLY (SELECT c.eid, c.Date, MAX(c.Time) OutTime FROM atend c WHERE a.eid = c.eid AND a.date = c.date GROUP BY c.eid, c.Date) e
    where a.eid='26153 '
    order by a.eid, a.date, d.InTime, e.OutTime



    eid-----------------------date-----------------------------------------intime------------------------------outtime
    26153------2013-01-01 00:00:00.000----2013-06-11 09:03:00.000----- 2013-06-11 19:54:00.000


    but actual data is this

    -eid-----------------date-------------------------------------time---------------------status
    26153-----2013-01-01 00:00:00.000-----2013-06-11 09:03:00.000------I
    26153-----2013-01-01 00:00:00.000-----2013-06-11 12:41:00.000-----O
    26153-----2013-01-01 00:00:00.000-----2013-06-11 13:57:00.000------I
    26153-----2013-01-01 00:00:00.000-----2013-06-11 19:54:00.000-----O


    i want this type of result



    eid---------------------date--------------------------------------intime-------------------------------------outtime
    26153------2013-01-01 00:00:00.000----2013-06-11 09:03:00.000----- 2013-06-11 12:41:00.000
    26153------2013-01-01 00:00:00.000----2013-06-11 13:57:00.000----- 2013-06-11 19:54:00.000



    i want total in and out
  2. davidfarr Member

    As before, I like to use #temp tables for these things.
    Try this;

    Code:
    --drop table #temp2
    select eid,[date],[time],[status],
    row_number() OVER (PARTITION by eid,[date],[status] ORDER BY [time]) as rIndex
    into #temp2
    from atend
    
    select t1.eid,t1.[date],t1.[time] as [intime],t2.[time] as [outtime] from #temp2 t1
    left join #temp2 t2 on t1.eid=t2.eid and t1.[date]=t2.[date] and t2.[status]='O' and t1.rIndex=t2.rIndex
    where t1.eid=26153
    and t1.[status]='I'
    order by t1.[date],t1.[rIndex]
    
  3. immad Member

    what is [rindex] it give me error
    this error
    Invalid column name 'rIndex'.
  4. FrankKalis Moderator

    Which version of SQL Server are you using?
  5. immad Member

    sql server 2005
  6. FrankKalis Moderator

    rIndex is the column alias for the ROW_NUMBER windowing function.Have you copied David's snippet in fully?
  7. immad Member

    ok i get it

Share This Page