DATA

• immaduddinahmed

  Ten Centuries

  Points: 1375

  More actions

  June 8, 2013 at 5:39 am

  #394933

  My data is like this

  eid-------date----------time--------status

  26359---2013-01-01----07:44--------IN

  26359---2013-01-01----18:50--------OUT

  26359---2013-01-02----07:47--------IN

  26359---2013-01-02----18:51--------OUT

  i want this type of data

  eid-------date-----------timein--------timeout

  26359---2013-01-01------07:44--------18:50

  26359---2013-01-02------07:47--------18:51

  i want timein and timeout side by side

  Thanks for the help

  immad

• Bhaskar.Shetty

  Hall of Fame

  Points: 3054

  More actions

  June 8, 2013 at 5:59 am

  #1622221

  Try this...

  DECLARE @TestTb TABLE

  (eid int, [date] date, [time] time, status char(3))

  INSERT INTO @TestTb (eid, [date], [time], status) VALUES

  (26359,'2013-01-01','07:44','IN'),

  (26359,'2013-01-01','18:50','OUT'),

  (26359,'2013-01-02','07:47','IN'),

  (26359,'2013-01-02','18:51','OUT')

  SELECT * from @TestTb

  SELECT a.eid, a.date, d.InTime, e.OutTime

  FROM

  (SELECT DISTINCT eid, date FROM @TestTb) a

  OUTER APPLY (SELECT b.eid, b.Date, MIN(b.Time) InTime FROM @TestTb 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 @TestTb c WHERE a.eid = c.eid AND a.date = c.date GROUP BY c.eid, c.Date) e

• immaduddinahmed

  Ten Centuries

  Points: 1375

  More actions

  June 8, 2013 at 6:24 am

  #1622223

  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

  Thanks in advance

  immad