sqlsnowflake-cloud-data-platformsnowflake-schema

Calculating 20 business days between 2 dates in Snowflake sql

  1. I would like to calculate if date 2 is greater than 20 business from date 1 in snowflake.

Sample data

Date1            Date2
2021-01-16       2021-04-01
2020-03-02       2020-04-05
2020-06-09       2020-06-30

I am trying something like this just to see if i can see 20 business days calculation right, but it is giving false results

SELECT *,
      DATEADD(DAY, 25 + DECODE(EXTRACT(DOW FROM DATE1), 4, 2, 5, 2, 6, 7, 8, 9, 10, 1, 0, 11, 12,13, 14,15,16, 17,18,19, 20),
      DATE1) BD_20
FROM (SELECT * FROM table);
Solution

  • Presumably, by business day, you mean Mon-Fri. If so, 20 business days is exactly 4 weeks, which is exactly 28 days.

    That would be:

    select t.*,
       (date2 > date1 + interval '28 day') as flag
from t;