Let's say I have table A in SSMS as such:

I would like for my output to be the following

Essentially I'd like to get the first START_DATE and last END_DATE for EMPLOYEE and TEACHING within each block. A way I tried to work around this was by creating two ranks, one ASC and one DESC but it would give me 1/1/2001 - 12/3/2003 for science which is not what I want.

History is breaking the chain as someone already commented.

CodePudding user response：

I would suggest following solution:

DECLARE @T TABLE
(
  EMPLOYEE int,
  TEACHING varchar(MAX),
  START_DATE datetime2,
  END_DATE datetime2
)

INSERT @T VALUES
(123, 'SCIENCE', '1/1/2001', '5/10/2001'),
(123, 'SCIENCE', '8/20/2001', '12/16/2001'),
(123, 'SCIENCE', '1/18/2002', '5/8/2002'),
(123, 'HISTORY', '8/15/2002', '12/10/2002'),
(123, 'SCIENCE', '1/3/2003', '5/6/2003'),
(123, 'SCIENCE', '8/1/2003', '12/3/2003'),
(124, 'SCIENCE', '1/1/2001', '5/10/2001'),
(124, 'SCIENCE', '8/20/2001', '12/16/2001'),
(124, 'SCIENCE', '1/18/2002', '5/8/2002'),
(124, 'HISTORY', '8/15/2002', '12/10/2002'),
(124, 'SCIENCE', '1/3/2003', '5/6/2003'),
(124, 'SCIENCE', '8/1/2003', '12/3/2003');

WITH Discriminated AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY EMPLOYEE ORDER BY START_DATE)-ROW_NUMBER() OVER (PARTITION BY EMPLOYEE, TEACHING ORDER BY START_DATE) Discriminator
  FROM @T
)
SELECT EMPLOYEE, TEACHING, MIN(START_DATE) START_DATE, MAX(END_DATE) END_DATE
FROM Discriminated
GROUP BY EMPLOYEE, TEACHING, Discriminator
ORDER BY EMPLOYEE, START_DATE

I also assume you would like to calculate each EMPLOYEE separately. The trick is to mark consecutive groups (see query in CTE).