This is the query I'm using but is totally wrong:
SELECT DateID = ROW_NUMBER() over (order by B.Date_Key) , B.ClosingDate, C.dates AS RecDay FROM DIM_DATE B JOIN [dbo].[WorkDay_Calendar] C on C.dates = DATEADD(DAY,3, B.ClosingDate) WHERE YEAR(B.ClosingDate) >= '2018' AND C.[Sentday] = 0 and C.[RecDay] = 0
This query is retrieving the RecDay when Closingdate +3 days = to Sentday AND What I want is when Closingdate + 3(Sentday) then pick the next RecDay, something like
C.dates = DATEADD(DAY,3(Sentday), B.ClosingDate).
This is how are looking my tables:
Sample input data:
Notice that when Sentday and RecDay are valid when = 0 if 1 is not valid because is a weekend or holiday.
Based on this information for example if I pick from the Dim_Date table 2018-02-02 as one of the Closingdate then the RecDay should be:
DateID RecDay 1 2018-02-07
And with the current query is retrieving this which is totally wrong:
DateID RecDay 1 2018-02-05
More output examples: Using the dates below as ClosingDate:
Date_Key ClosingDate: 38284 2018-07-24 38287 2018-01-10 38290 2018-03-08 38291 2018-07-13 38293 2018-02-08
Using the same order of the ClosingDates these should be the outputs, I incluided the ClosingDate column so you can follow the order: OUTPUTS:
DateID ClosingDate RecDay (output) 1 2018-07-24 2018-07-30 2 2018-01-10 2018-01-16 3 2018-03-08 2018-03-13 4 2018-07-13 2018-07-18 5 2018-02-08 2018-02-13