Select one line from two lines based on date

Question

I have a table with the following data below where I would like the status changes of 2 lines to be one line based on the start date.
Here is the table script and output. The grouping will be per ClientID

CREATE TABLE Accountx
( ID INT,
CompanyID INT,
[Status] nvarchar(10),
ClientID INT,
StartDate DateTime
) ON [PRIMARY]
INSERT INTO Accountx VALUES(1,1,'New', 44,'2020-06-18 11:54:00'),
   (2,1,'Active',44,'2020-06-18 11:57:00'),
   (3,1,'Inactive',44,'2020-06-18 12:09:00')

Data Output

ID  CompanyID   Status  ClientID    StartDate
1    1          New       44        2020-06-18 11:54:00.000
2    1          Active    44        2020-06-18 11:57:00.000
3    1          Inactive  44        2020-06-18 12:09:00.000

Desired Output
The EndDate is the StartDate Value of the ToStatus Column

  CompanyID  FromStatus ToStatus ClientID StartDate            EndDate
    1        New        Active     44     2020-06-18 11:54:00  2020-06-18 11:57:00
    1        Active     Inactive   44     2020-06-18 11:57:00  2020-06-18 12:09:00
    1        Inactive   NULL       44     2020-06-18 11:54:00  NULL

Aren't you just after `LEAD` here? – Thom A Sep 01 '22 at 14:33 — Thom A, Sep 01 '22 at 14:33
Yes, first time I hear of it – Miles Colby Sep 01 '22 at 15:18 — Miles Colby, Sep 01 '22 at 15:18

score 0 · Accepted Answer · answered Sep 01 '22 at 14:40

0

LEAD and LAG methods are perfect for these types of questions.

SELECT CompanyID, Status, 
LEAD(Status) OVER(ORDER BY StartDate) AS ToStatus
, ClientID, StartDate, 
LEAD(StartDate) OVER(ORDER BY StartDate) AS EndDate
FROM Accountx

answered Sep 01 '22 at 14:40

Helio

621
1
4
24

Select one line from two lines based on date

1 Answers1