I have a calendar table with all dates and a column indicating whether its a business day.
For example

... I have another table with dates and would like to determine how many business days have elapsed since the beginning of that month. For example:

Is there a way to do this using standard sql? I was thinking of creating some kind of UDF but there may be an easier way? This using snowflake so there is access to all common date functions and window/udf functions

CodePudding user response：

Here is a simple query

Select count(c.date) from calendar c 
Join dates_to_check d
Where month(c.date) = month(d.day)
And day(c.date) < day(d.day)
And business_Day = True;

CodePudding user response：

I think you can accomplish this with a simple join and a window function. I am assuming your date is formatted yyyy-mm-dd which would allow left(date,7) to get the year-month. If not, just use a different function to extract year-month for aggregating per month.

select a.date, 
       count(b.business_day) over (partition by left(a.date,7) order by a.date) business_days_elapsed
from table a
     left join calendar_table b on a.date=b.date and b.business_day='True'
group by a.date, b.business_day; --in case your data is not deduped before window function is applied

CodePudding user response：

try this with the single, table you can create a view

create or replace table bus_day as
SELECT column1  Date, column2   Business_Day  from values
('1/1/2021',    'False'),
('1/2/2021',    'False'),
('1/3/2021',    'True'),
('1/4/2021',    'True'),
('1/5/2021',    'True'),
('1/6/2021',    'True'),
('1/7/2021',    'True'),
('2/1/2021',    'True'),
('2/2/2021',    'True'),
('2/3/2021',    'True'),
('2/4/2021',    'True'),
('2/5/2021',    'False'),
('2/6/2021',    'False'),
('2/7/2021',    'True')
;
 
  SELECT DATE,FIRST_DAY_MONTH 
 ,row_number() over (partition by month(to_date(Date,'mm/dd/yyyy')) order by date) as row_number
 ,count_if(business_day ='True') over (partition by month(to_date(Date,'mm/dd/yyyy'))   order by DATE ) count_days
FROM (
  select Date , DATE_TRUNC('month',to_date(Date,'mm/dd/yyyy')) frst_day,
  iff(to_date(Date,'mm/dd/yyyy') = DATE_TRUNC('month',to_date(Date,'mm/dd/yyyy')), 'Y', 'N') first_day_Month , 
  business_day from bus_day order by date
   );

CodePudding user response：

So if your dates has "all dates" you can use a JOIN like:

SELECT d.date,
    count_if(w.business_day) AS business_days_passed
FROM dates_of_interest AS d
JOIN work_days AS w
    ON date_trunc(month, d.date) = date_trunc(month, w.date) 
        AND d.date >= w.date
GROUP BY 1
ORDER BY 1;

But if you Dates have gaps in them, like my example data does... you would want the LEFT JOIN form..

SELECT d.date,
    count_if(w.business_day) AS business_days_passed
FROM dates_of_interest AS d
LEFT JOIN work_days AS w
    ON date_trunc(month, d.date) = date_trunc(month, w.date) 
        AND d.date >= w.date
GROUP BY 1
ORDER BY 1;

And using this wonderful fake data:

WITH work_days(date, business_day) AS (
    SELECT TO_DATE(column1, 'mm/dd/yyyy'), column2
    FROM VALUES
        ('1/1/2022', False),
        ('1/2/2022', False),
        ('1/3/2022', True),
        ('1/4/2022', True),
        ('1/5/2022', True),
        ('1/6/2022', True),
        ('1/7/2022', True),
       ('1/10/2022', True),
       ('1/11/2022', True),
       ('1/12/2022', True),
       ('1/13/2022', True),
       ('1/13/2022', True)    
), dates_of_interest(date) AS (
    SELECT TO_DATE(column1, 'mm/dd/yyyy')
    FROM VALUES 
        ('1/1/2022'),
        ('1/2/2022'),
        ('1/3/2022'),
        ('1/4/2022'),
        ('1/15/2022'),
        ('2/2/2022'),
        ('2/11/2022'),
        ('2/12/2022')
)

we get: