Search code examples
sqldatabaserdbms

SQL to show status for both id and next_id in a single row

Can someone help me on below please: I tried the below SQL

select 
    CUSTOMER,
    ID as Current_ID, 
    lead(ID) over (partition by ID order by CUSTOMER, ID) as Next_ID,
    STATUS, 
    START_DATE, 
    END_DATE
From Source_Table

My table has values like below Source table data

The expected outcome should be Expected Outcome

Solution

  • Using your example data:

    create table #Source_Table
(
    CUSTOMER varchar(20),
    ID int,
    [STATUS] varchar(20),
    [START_DATE] date,
    END_DATE date
)

insert into #Source_Table values
('John',10,'Delivered','2022-04-01','2023-03-31'),
('John',20,'Requested','2023-04-01','2024-03-31'),
('Martin',10,'Delivered','2023-05-01','2023-08-31'),
('Peter',10,'Delivered','2023-04-03','2023-06-30'),
('Peter',20,'Delivered','2023-07-01','2023-08-31'),
('Peter',30,'Requested','2023-09-01','2023-03-31')

    This query will return every record, with the next ID and STATUS where applicable:

    select
    CUSTOMER,
    ID as CURRENT_ID,
    [STATUS] as CURRENT_ID_STATUS,
    lead(ID) over (partition by CUSTOMER order by ID) as NEXT_ID,
    lead([STATUS]) over (partition by CUSTOMER order by ID) as NEXT_ID_STATUS,
    [START_DATE],
    END_DATE
from #Source_Table

    Results:

    CUSTOMER CURRENT_ID CURRENT_ID_STATUS NEXT_ID NEXT_ID_STATUS START_DATE END_DATE
    John 10 Delivered 20 Requested 01/04/2022 31/03/2023
    John 20 Requested NULL NULL 01/04/2023 31/03/2024
    Martin 10 Delivered NULL NULL 01/05/2023 31/08/2023
    Peter 10 Delivered 20 Delivered 03/04/2023 30/06/2023
    Peter 20 Delivered 30 Requested 01/07/2023 31/08/2023
    Peter 30 Requested NULL NULL 01/09/2023 31/03/2023

    It seems from your expected output that you then want to filter this in some way, but without you explaining the criteria for that filtering, I don't think anyone can help you further.