What is the best approach to come up with the output below in SQL Server?

Query from Table 1 below and have an out like Table 2.

Last IN First OUT Rule

As described in this image

CodePudding user response：

For this set a simple CASE statement can be used to separate the INs from the OUTs:

SELECT [Date]
      , MAX(CASE [IN/OUT] WHEN 'IN' THEN [Time] END) AS [IN]
      , MIN(CASE [IN/OUT] WHEN 'OUT' THEN [Time] END) AS [OUT]
FROM Times
GROUP BY [Date]
ORDER BY [Date]

When the conditions get more complicated I prefer to use CTE syntax:

WITH TimeData as (
  SELECT [Date],[Time],[IN/OUT]
    , CASE [IN/OUT] WHEN 'IN' THEN [Time] END AS IN_TIME 
    , CASE [IN/OUT] WHEN 'OUT' THEN [Time] END AS OUT_TIME 
  FROM Times
)
 SELECT [Date]
      , MAX(IN_TIME) AS [IN]
      , MIN(OUT_TIME) AS [OUT]
 FROM TimeData
 GROUP BY [Date]
 ORDER BY [Date];

Both produce the same result set: (see http://sqlfiddle.com/#!18/52750/1 )