I am trying to get the next available date from a calculation that I show acontinuacion.
That's the query I'm using but it does not work.
SELECT DateID = ROW_NUMBER() over (order by B.Date_Key) ,C.FundDate FROM DIM_DATE B LEFT JOIN DIM_Status S ON B.Date_Key = S.StatusID LEFT JOIN [CALENDAR] C on C.[SentDay] = CASE
WHEN
DATEADD(DAY, 3, S.ClosingDate) IS NULL THEN DATEADD(DAY, 3, B.SchedClosingDate)
ELSE DATEADD(DAY, 3, S.ClosingDate)
END
In this query what I am trying to obtain is for each day of closing (ClosingDate or SchedClosingDate), from that date count the next 3 days sent (Sentday) then after those 3 days sent the next available day of that result (fundDate) for each DateID,
Here I also leave some of the values and tables that I am using.
DIM_DATE Table:
Date_Key SchedClosingDate
35523 null
35524 null
35525 null
35526 2017-07-31
35527 2017-06-14
35528 2018-01-12
35529 2018-01-04
Dim_Status Table:
StatusID ClosingDate
35951 null
35952 2017-09-15 00:00:00
35953 null
35954 2018-03-15 00:00:00
35955 null
35956 null
35957 2018-05-15 00:00:00
Calendar Table:
ID SentDay FundDate
92 2018-04-20 2018-05-11
93 2018-04-21 2018-05-14
94 2018-04-23 2018-05-15
95 2018-04-24 2018-05-16
96 2018-04-25 2018-05-17
97 2018-04-26 2018-05-18
98 2018-04-27 2018-05-21
If I took the value 2018-05-15 00:00:00 as ClosingDate example the result would have to be something like this:
DateID FundDate
1 2018-05-21
Please keep in mind that those dates already have the weekend and holidays excluded to avoid a lot of code.
Any suggestion or help would appreciate you very much, thank you.