Getting a date + 3 days ( using specific date) SQL

Question

question

JCACERES asked Jun 27, '18

Getting a date + 3 days ( using specific date) SQL

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:

Dim_Date TABLE

WorkDay_Calendar Table

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

sql tsql sqlserver

dimdate.png (9.3 KiB)

calendar-workday.png (16.5 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-06-25T07:05:41Z

WRBI answered Jun 25, '18

Sorry you're example is confusing me a little.

It could just be the naming of your columns. Sent in your example has dates AFTER received. Every process/system that I can think would have sent BEFORE received.

You have Sent Day and Receive Day in your calendar table and the sent day is always the day AFTER the receive day for the 03/04. However, even by the examples there, the 05-01-18 should have the 4th? Because the 5th is still a working day and follows the same pattern as its predecessors.

I think you're your going down the right lines having the dates in a date dimension table. I'd have the receive date in there, join your sent date the date key(however you format your date key) and then return the receive date. It stops you needing to do business logic in the query. Also, if you're business logic changes you just update all the dates in the received column and bam, its done in one place meaning you down have to update mulitple calculations throughout the system.

datedimension.png (7.9 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JCACERES commented · Jun 25, 2018 at 03:14 PM

I just did some changes on the query but still doing wrong.

0 ·

question