Alter row based on column-CodePudding

I am trying to alter the rows with the following conditions in SQL Server.

Merge all rows with similar code taking first date from the 1st row and last date from the last row.
Un-similar rows will remain as it is.

CREATE TABLE ABCD(
    id int,
    FirstDate date,
    LastDate date,
    code varchar(23)
);

Insert into ABCD VALUES
(1, '2022-12-12','2022-12-12', 'A'),
(2, '2022-12-13','2022-12-13', 'A'),
(3, '2022-12-15','2022-12-15', 'A'),
(4, '2022-12-16','2022-2-16', 'B'),
(5, '2022-12-18','2022-12-18', 'A'),
(5, '2022-12-19','2022-12-19', 'A'),
(6, '2022-12-20','2022-12-20', 'C')

SELECT * from ABCD

Expected result is

(1, '2022-12-12','2022-12-15', 'A'),
(2, '2022-12-16','2022-2-16', 'B'),
(3, '2022-12-18','2022-12-19', 'A'),
(4, '2022-12-20','2022-12-20', 'C')

Tried

SELECT *
FROM ABCD #A
INNER JOIN ABCD #B on #A.code != #B.code AND #A.id = #B.id

I'm unable to think of any logic to get this working. Suggestions are highly appreciated. Thanks!

CodePudding user response：

You can do it with logic that has a few steps

First, 'group' the rows together based on whether they're sequential (e.g., the first three A rows, then the B row, etc)
- To do this, the answer below uses 2 steps: first, find each row that is a 'new' group, and assign those rows a 1. Then do a running total on those - which becomes the grouping value.
Then, group by that grouping value - and get the minimum and maximum dates.

WITH ABCD_with_newvals AS
  (SELECT *, 
         CASE WHEN LAG(code, 1) OVER (ORDER BY ID) = [code] THEN 0 ELSE 1 END AS NewVal
  FROM ABCD
  ),
  ABCD_grouped AS
  (SELECT *,
         SUM(NewVal) OVER (ORDER BY ID) AS GroupNum
  FROM ABCD_with_newvals
  )
SELECT GroupNum, Code, MIN(FirstDate) AS FirstDate, MAX(LastDate) AS LastDate
  FROM ABCD_grouped
  GROUP BY GroupNum, Code
  ORDER BY GroupNum;

Here is a db<>fiddle with the answer, as well as breaking the steps up so you can see what the data is at each point in the process.

Oh - here are the results

GroupNum   Code   FirstDate    LastDate
1          A      2022-12-12   2022-12-15
2          B      2022-12-16   2022-02-16
3          A      2022-12-18   2022-12-19
4          C      2022-12-20   2022-12-20