Geek asks and answers

Combine adjacent rows in SQL?

I make a report based on employee work blocks. In some cases, the data contains two separate records for what really represents a single block of time.

Here is the basic version of the table and some sample entries:

EmployeeID
StartTime
EndTime

Data:

EmpID      Start         End
----------------------------
#1001   10:00 AM    12:00 PM
#1001    4:00 PM     5:30 PM
#1001    5:30 PM     8:00 PM

In this example, the last two entries are adjacent in time. I would like to write a query that combines any adjacent records, so the result set is as follows:

EmpID      Start         End
----------------------------
#1001   10:00 AM    12:00 PM
#1001    4:00 PM     8:00 PM

Ideally, it should also be able to handle more than two adjacent records, but this is not required.

+5
source share
5 answers

This article presents several possible solutions to your question.

http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851

:

WITH StartTimes AS
(
  SELECT DISTINCT username, starttime
  FROM dbo.Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.starttime < S1.starttime
       AND S2.endtime >= S1.starttime)
),
EndTimes AS
(
  SELECT DISTINCT username, endtime
  FROM dbo.Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.endtime > S1.endtime
       AND S2.starttime <= S1.endtime)
)
SELECT username, starttime,
  (SELECT MIN(endtime) FROM EndTimes AS E
   WHERE E.username = S.username
     AND endtime >= starttime) AS endtime
FROM StartTimes AS S;
+2

( ), :

  • .

  • , .

  • , Start End.

, Transact-SQL, - :

WITH unpivoted AS (
  SELECT
    EmpID,
    event,
    dtime,
    count = COUNT(*) OVER (PARTITION BY EmpID, dtime)
  FROM atable
  UNPIVOT (
    dtime FOR event IN (StartTime, EndTime)
  ) u
)
, filtered AS (
  SELECT
    EmpID,
    event,
    dtime,
    rowno = ROW_NUMBER() OVER (PARTITION BY EmpID, event ORDER BY dtime)
  FROM unpivoted
  WHERE count = 1
)
, pivoted AS (
  SELECT
    EmpID,
    StartTime,
    EndTime
  FROM filtered
  PIVOT (
    MAX(dtime) FOR event IN (StartTime, EndTime)
  ) p
)
SELECT *
FROM pivoted
;

SQL Fiddle.

+1

lil ' , , , :

with cte as (
  select 
    x1.id
    ,x1.t1
    ,x1.t2
    ,case when x2.t1 is null then 1 else 0 end as bef
    ,case when x3.t1 is null then 1 else 0 end as aft
  from x x1
  left join x x2 on x1.id=x2.id and x1.t1=x2.t2
  left join x x3 on x1.id=x3.id and x1.t2=x3.t1
  where x2.id is null
  or    x3.id is null
)

select 
  cteo.id
  ,cteo.t1
  ,isnull(z.t2,cteo.t2) as t2

from cte cteo
outer apply (select top 1 * 
             from cte ctei 
             where cteo.id=ctei.id and cteo.aft=0 and ctei.t1>cteo.t1
             order by t1) z
where cteo.bef=1

: http://sqlfiddle.com/#!3/ad737/12/0

0
source

Option with built-in custom function AND CTE

CREATE FUNCTION dbo.Overlap
 (
  @availStart datetime,
  @availEnd datetime,
  @availStart2 datetime,
  @availEnd2 datetime
  )
RETURNS TABLE
RETURN
  SELECT CASE WHEN @availStart > @availEnd2 OR @availEnd < @availStart2
              THEN @availStart ELSE
                               CASE WHEN @availStart > @availStart2 THEN @availStart2 ELSE @availStart END
                               END AS availStart,
         CASE WHEN @availStart > @availEnd2 OR @availEnd < @availStart2
              THEN @availEnd ELSE
                             CASE WHEN @availEnd > @availEnd2 THEN @availEnd ELSE @availEnd2 END
                             END AS availEnd

;WITH cte AS
 (
  SELECT EmpID, Start, [End], ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Start) AS Id
  FROM dbo.TableName
  ), cte2 AS
 (
  SELECT Id, EmpID, Start, [End]
  FROM cte
  WHERE Id = 1
  UNION ALL
  SELECT c.Id, c.EmpID, o.availStart, o.availEnd
  FROM cte c JOIN cte2 ct ON c.Id = ct.Id + 1
             CROSS APPLY dbo.Overlap(c.Start, c.[End], ct.Start, ct.[End]) AS o
  )
  SELECT EmpID, Start, MAX([End])
  FROM cte2
  GROUP BY EmpID, Start

Demo on SQLFiddle

0
source

CTE with a total of:

DECLARE @t TABLE(EmpId INT, Start TIME, Finish TIME)
INSERT INTO @t (EmpId, Start, Finish)
VALUES
    (1001, '10:00 AM', '12:00 PM'),
    (1001, '4:00 PM', '5:30 PM'),
    (1001, '5:30 PM', '8:00 PM')

;WITH rowind AS (
    SELECT EmpId, Start, Finish,
        -- IIF returns 1 for each row that should generate a new row in the final result
        IIF(Start = LAG(Finish, 1) OVER(PARTITION BY EmpId ORDER BY Start), 0, 1) newrow
    FROM @t),
    groups AS (
    SELECT EmpId, Start, Finish,
        -- Cumulative sum
        SUM(newrow) OVER(PARTITION BY EmpId ORDER BY Start) csum
    FROM rowind)

SELECT
    EmpId,
    MIN(Start) Start,
    MAX(Finish) Finish
FROM groups
GROUP BY EmpId, csum
0
source

More articles:


All Articles