Get a particular date for each SQL ID

0

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.

    
asked by JCaceres 25.06.2018 в 19:12
source

0 answers