oracle join the same table but with diffferent date

Question

I have a simple table that contain two columns Cust_Id and Sales:

custID_1| sales_1 |sales_date_1| 
01      | 100     |01/2021     | 
02      | 102     |02/2021     | 
07      | 10      |04/2021     | 
10      | 180     |05/2021     | 
12      | 90      |06/2021     | 



custID_2| sales_2 |sales_date_2| 
05      | 400     |02/2022     | 
06      | 110     |03/2022     | 
08      | 300     |04/2022     | 
11      | 80      |06/2022     | 

and I want to show the result at once in two different dates like below:

custID_1| custID_2 |sales_1 |sales_2 |sales_date_1|sales_date_2
01      | null     |100     |null    |01/2021     |null    
02      | 05       |102     |400     |02/2021     |02/2022
null    | 06       |null    |110     |null        |03/2022
07      | 08       |10      |300     |04/2021     |04/2022  
10      | null     |180     |null    |05/2021     |null        
12      | 11       |190     |80      |06/2021     |06/2022

here is what I tried below but I get duplicated value:

Select S1.Cust_Id "custID_1", s2.Cust_Id "custID_2",
s1.Sales "sales_1", s2.Sales "sales_2",
s1.Sale_Date "sales_date_1", s2.Sale_Date "sales_date_2"
From Sales_Table S1 Left Join Sales_Table S2
On S1.Cust_Id = S2.Cust_Id
And S1.Sale_Date Between '01-01-21' And '31-10-21'
And S2.Sale_Date Between '01-01-22' And '31-10-22'

Answer 1

The dates in your sample data are confusing, but I guess that you want to join both tables on the same date on different years.

Assuming, of course, that your dates are stored as a date-like datatype, you can full join and use date arithmetics:

select s1.cust_id cust_id_1, cust_id_2, 
    s1.sales sales_1, s2.sales sales_2,
    s1.sale_date sale_date_1, s2.sale_date sales_date_2
from (
    select * 
    from sales_table 
    where sale_date >= date '2021-01-01' and sale_date < '2022-01-01'
) s1
full join (
    select * 
    from sales_table 
    where sale_date >= date '2022-01-01' and sale_date < '2023-01-01'
) s2 on s2.sale_date = s1.sale_date + interval '1' year

Notes:

• this only works if there is at most 1 sale per date in each table ; otherwise, rows would end up "multiplied" in the resultset
• I pre-filtered the tables ; but if each table has data for one year only, then this step is not needed