I have one table that contains customer id and start date and one table that contains customer id and end date.

table A

table B

Is there a way that I can get an output that looks like below?

desired output

CodePudding user response：

This hint is using TSQL in SQL Server.

select A.customer_id, A.start_date, B.end_date
from 
    (select X.costumer_id, X.start_date, ROW_NUMBER() over (order by X.start_date) as ORDEM from TableA X) A
    left outer join (select X.customer_id, X.end_date, ROW_NUMBER() over (order by X.end_date ) as ORDEM  from TableB X) B on A.customer_id = B.customer_id and A.ORDEM = B.ORDEM
order by A.customer_id, A.start_date

Hope it helps.

CodePudding user response：

As per your desire result please check the below query you can change it as per your table name and requirements.

DECLARE @table1 TABLE(
    [customer_id] INT,
    [start_date] DATE 
)

DECLARE @table2 TABLE(
    [customer_id] INT,
    [end_date] DATE 
)

INSERT INTO @table1 VALUES
(1,'2022-01-01'),
(1,'2022-04-01'),
(1,'2022-07-01'),
(2,'2022-01-15'),
(2,'2022-03-25'),
(3,'2022-04-01'),
(3,'2022-08-01'),
(4,'2022-09-01')

INSERT INTO @table2 VALUES
(1,'2022-01-25'),
(1,'2022-05-03'),
(2,'2022-03-24'),
(2,'2022-03-29'),
(3,'2022-04-15')

SELECT [Table1].[customer_id],[Table1].[start_date],[Table2].[end_date] FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [start_date]) row_num FROM @table1
) AS [Table1]
LEFT JOIN (
    SELECT t2.*, ROW_NUMBER() OVER (ORDER BY [end_date]  ) row_num FROM @table2 t2
) AS [Table2]
ON [Table2].[customer_id] = [Table1].[customer_id] 
AND [Table1].[row_num] = [Table2].[row_num]
ORDER BY [Table1].[customer_id]

Output