2

I have a variable called "BILL_ISSUE_DATE" and I would like to add 3 business days to this date. I need the new date for a "Wait by Attribute" in the journey.

So lets say BILL_ISSUE_DATE = November the 16th (Saturday) then the new date should be November the 20th ( Wednesday)

For December the 16th (Monday) the new date should be December the 19th Thursday.

I have used this SQL below to calculate the 9th of next month, but i'm not sure how to in corporate "BILL_ISSUE_DATE" to add 3 business days

[9th of the next month - not weekend] = 
 CASE WHEN ((datediff(day, -1, DATEFROMPARTS(YEAR(DATEADD(mm,1,GETDATE())),MONTH(DATEADD(mm,1,GETDATE())),9)) % 7) + 1) between 2 and 6 
    THEN DATEFROMPARTS(YEAR(DATEADD(mm,1,GETDATE())),MONTH(DATEADD(mm,1,GETDATE())), 9)
         WHEN ((datediff(day, -1, DATEFROMPARTS(YEAR(DATEADD(mm,1,GETDATE())),MONTH(DATEADD(mm,1,GETDATE())),9)) % 7) + 1) = 1
    THEN DATEFROMPARTS(YEAR(DATEADD(mm,1,GETDATE())),MONTH(DATEADD(mm,1,GETDATE())), 9 + 1)
         WHEN ((datediff(day, -1, DATEFROMPARTS(YEAR(DATEADD(mm,1,GETDATE())),MONTH(DATEADD(mm,1,GETDATE())),9)) % 7) + 1) = 7
    THEN DATEFROMPARTS(YEAR(DATEADD(mm,1,GETDATE())),MONTH(DATEADD(mm,1,GETDATE())), 9 + 2)
  END
  • Just a comment: What if there is a public holiday? Just think about the side effects ;) – Johannes Schapdick Nov 15 '19 at 10:54
  • Yes I know - in this case that doesn't matter. But if you have a solution to add holidays feel free to do it :D – Martin Ho Nov 15 '19 at 10:57
  • 1
    You would create a table with public holidays and if a period falls into public holidays you would add the amount of days ;), but no i am not going to code this just for this thread ;) – Johannes Schapdick Nov 15 '19 at 11:10
0

Here is the working code, that will add 3 business day to a date

[Wait 3 days - not on weekends] =
          CASE WHEN DATEPART(dw,DATEADD(dd,3,BILL_ISSUE_DATE)) in (2,3,4,5,6)
   THEN DATEADD(dd,3+0,DATEADD(hh,6,BILL_ISSUE_DATE))
        WHEN DATEPART(dw,DATEADD(dd,3,BILL_ISSUE_DATE)) = 1
   THEN DATEADD(dd,3+1,DATEADD(hh,6,BILL_ISSUE_DATE))
        WHEN DATEPART(dw,DATEADD(dd,3,BILL_ISSUE_DATE)) = 7
   THEN DATEADD(dd,3+2,DATEADD(hh,6,BILL_ISSUE_DATE))
 END
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.