data

[immad]

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

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


please impelement on this query

thank you

 

[bborissov]

What is wrong with the solution in thread183-1712410

Borislav Borissov
VFP9 SP2, SQL Server