How to join the start date to another data if it not matched

Question

How to join the start date to another data if it not matched

dannyfirst80

SSC Veteran

Points: 294
More actions
December 18, 2020 at 8:38 pm

#3822197

I have a main table with two columns Support_Start_Date and Support_End_Date and I need to join to Date dim table and has two columns as start_date and end_date.
I want to use Date dim End_date when Start_Date din't match but I am not able to achieve Can you please?
Main Table:
Date Table:

Below is my query:
select
I.Inv_Crd_No,
SD.Year_Num as Start_Year,
SED.Month_Num as Start_Month,
SD.Year_Num as Start_Year,
SD.Period_Key as Supp_start_key,
ED.Year_Num as End_Year,
ED.Month_Num as End_Month,
ED.Period_Key as Supp_end_key,
I.Support_Start_Date,
I.Support_End_Date
from
Invoice_Credit_Fact I
LEFT JOIN Product_Ref PR ON I.Product_Key = PR.Product_Key
LEFT JOIN Period_Ref SD ON I.Support_Start_Date = SD.START_DATE
LEFT JOIN Period_Ref ED ON I.Support_END_Date = ED.END_DATE
where
INV_CRD_NO IN ('SIN020693','SIN020685')
order by
I.Support_Start_Date
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2160 More actions · Answer 1

What if there is more than one 'Period Key' for a (Support_Start_Date, Support_End_Date) pair?

Maybe you're looking for something like this? It returns multiple rows if the (Support_Start_Date, Support_End_Date) pair spans multiple 'Period Key'

select i.inv_Crd_No, 
       pr.Year_Num as Start_Year, 
       pr.Month_Num as Start_Month,
       pr.Period_Key as Supp_start_key,
       i.Support_Start_Date,
       i.Support_End_Date
from invoice_Credit_Fact i
     left join Period_Ref pr ON i.Support_Start_Date >= pr.[Start_Date]
                                and i.Support_End_Date = pr.End_Date
where i.inv_Crd_No iN ('SiN020693','SiN020685')
order by i.inv_Crd_No, i.Support_Start_Date;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Sergiy SSC Guru Points: 110208 More actions · Answer 2

LEFT JOIN Period_Ref SD ON I.Support_Start_Date = SD.START_DATE

LEFT JOIN Period_Ref ED ON SD.START_DATE IS NULL AND I.Support_END_Date = ED.END_DATE

_____________
Code for TallyGenerator