separat data as per column base

• akhterhussain80

  Mr or Mrs. 500

  Points: 504

  More actions

  December 17, 2018 at 6:12 am

  #373249

  i have data in colum                                  

  EmpID	DateTime	StatusINOUT
  1001	2018-05-26 09:33	1
  1001	2018-05-26 08:44	2
  1002	2018-05-28 08:22	1

  1002	2018-05-28 12:44	2
  1001	2018-05-21 07:44	1
  1001	2018-05-21 10:44	2
  1002	2018-05-23 11:11	1

  1002

  2018-05-23 09:44

  2

   

  Now i want to display Status wise INTIME(1) and OUTTIME(2) it  as below

  Empid	INTIME(1)	OUTIME(2)
  1	2018-05-26 08:33	2018-05-26 08:44
  2	2018-05-28 08:22	2018-05-28 12:44
  1	2018-05-21 07:44	2018-05-21 10:44
  2	2018-05-23 11:11	2018-05-23 09:44

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  December 17, 2018 at 6:30 am

  #2016486

  SELECT *
INTO #t
FROM (VALUES
(1001, CONVERT(datetime,'2018-05-26 09:33'), 1),
(1001, '2018-05-26 08:44', 2),
(1002, '2018-05-28 08:22', 1),
(1002, '2018-05-28 12:44', 2),
(1001, '2018-05-21 07:44', 1),
(1001, '2018-05-21 10:44', 2),
(1002, '2018-05-23 11:11', 1),
(1002, '2018-05-23 09:44', 2)) T(EmpID, myDateTime, StatusINOUT)

  ;WITH CTE AS
(
    SELECT EmpID,
           CASE WHEN StatusINOUT= 1 THEN myDateTime ELSE NULL END [INTIME],
           CASE WHEN StatusINOUT= 2 THEN myDateTime ELSE NULL END [OUTTIME],
           CONVERT(date,myDateTime) myDate
      FROM #t
)
SELECT x.EmpID,
       MAX(x.[INTIME]) [INTIME],
       MAX(x.[OUTTIME]) [OUTTIME]
  FROM CTE x
 GROUP BY EmpId, myDate
GO
DROP TABLE #t