Search code examples
sqlt-sqlsql-server-2008gaps-and-islands

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

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.

Date Time In/Out
8/1/2022 0800H IN
8/1/2022 0802H IN
8/1/2022 1700H OUT
8/2/2022 0810H IN
8/2/2022 1700H OUT
8/3/2022 0800H IN
8/3/2022 1700H OUT
8/3/2022 1710H OUT

Last IN First OUT Rule

Date IN OUT
8/1/2022 0802H 1700H
8/2/2022 0810H 1700H
8/3/2022 0800H 1700H

As described in this image original requirement

Solution

  • 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 )

    Date IN OUT
    2022-08-01 0802H 1700H
    2022-08-02 0810H 1700H
    2022-08-03 0800H 1700H