Remaining Business Days This Month in Amazon Redshift

Question

I would like to find a way to determine the remaining number of business days in this calendar month using Redshift. I currently have a MySQL version written by a friend. I don't know enough about how it was written to even translating into the other dialect. But if anyone could help translating it, this would a very useful tool!

The output should function just like the networkdays() function from excel. In this function, a begin_date and an end_date are provided as arguments for the function. It calculates the number of business days (non-weekend calendar days) between the begin and end dates inclusively.

Here is the current MySQL:

SELECT 1 AS pk ,COUNT(*) AS remaining
FROM (
    SELECT WEEKDAY(DATE(DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'PST8PDT'), '%Y-%m-01') + INTERVAL (a.num-1) DAY)) AS weekdays
        FROM (
        SELECT @row := @row + 1 AS num
        FROM schema.table t, (SELECT @row := 0) r
    ) a
        WHERE a.num >= DAY(CONVERT_TZ(NOW(), 'UTC', 'PST8PDT'))
        AND a.num <= DAY((DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'PST8PDT'), '%Y-%m-01') + INTERVAL 1 MONTH) - INTERVAL 1 DAY)
) b
WHERE b.weekdays NOT IN (0,6)

Any help would be great!

Answer 1

A popular idea for data warehouses is to create a calendar table that contains all dates (or at least dates relevant to the company), together with flags such as:

• Public Holidays
• Work days
• First (work) day of month
• Last (work) day of month
• Month number
• Week number
• Day number

While many of these values can be calculated via date functions, it can often be easier to join to the calendar table to perform some date functions.

In the case of calculating remaining business days, it would just be a matter of counting the number of rows in the calendar table that are within the desired range, where the is_work_day flag is set. This could be done via a JOIN or a sub-query.

Not as fancy as some of those queries but often much easier to maintain. Also, Amazon Redshift does not support the generate_series function, so it's often all that's possible.

See also:

• Stack Overflow Calendar table for Data Warehouse
• MSQL Tips Creating a date dimension or calendar table in SQL Server

Answer 2

In order to get a count of business days, you need to know a date of the 1st day (start_date) of the month using date_trunc() function. After that, you need to get a number of days for particular month (month_last_day) using extract() function,there is a wiki page regarding this. And finally, you can generate_series() of days using start_date date and month_last_day number excluding the weekend days using date_part() function.

CREATE OR REPLACE FUNCTION extract_month_business_days(d DATE, count_remaining BOOLEAN)
  RETURNS INTEGER AS $$
DECLARE
  start_date DATE;
  month_last_day INTEGER;
  result INTEGER;
BEGIN
  IF count_remaining THEN
    start_date = d;
  ELSE
    start_date = date_trunc('month',d);
  END IF;
  month_last_day = extract(DAY FROM date_trunc('month',d) + INTERVAL '1 MONTH - 1 day');
  SELECT count(*) INTO result FROM generate_series(0,(month_last_day - extract(DAY FROM start_date))::INTEGER) day
    WHERE date_part('dow', start_date + day) NOT IN (0,6);
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Result:

WITH t(dates) AS ( VALUES
  ('2016-02-18'::DATE),
  ('2016-03-18'::DATE),
  ('2016-04-18'::DATE),
  ('2016-05-18'::DATE)
)
SELECT
  to_char(dates,'Month YY') AS month,
  extract_month_business_days(dates,FALSE) AS number_business_days,
  extract_month_business_days(dates,TRUE) AS remaining_business_days
FROM t;

    month     | number_business_days | remaining_business_days 
--------------+----------------------+-------------------------
 February  16 |                   21 |                       8
 March     16 |                   23 |                      10
 April     16 |                   21 |                      10
 May       16 |                   22 |                      10
(4 rows)

UPDATE - REDSHIFT EDITION

As @John pointed out, generate_series() is not available in AWS Redshift, function definition will be the following:

CREATE OR REPLACE FUNCTION extract_month_business_days(d DATE, count_remaining BOOLEAN)
  RETURNS INTEGER AS $$
DECLARE
  start_date DATE;
  month_last_day INTEGER;
  result INTEGER;
  i INTEGER;
BEGIN
  result = 0;
  IF count_remaining THEN
    start_date = d;
  ELSE
    start_date = date_trunc('month',d);
  END IF;
  month_last_day = extract(DAY FROM date_trunc('month',d) + INTERVAL '1 MONTH - 1 day');
  result = 0;
  FOR i IN 0..(month_last_day - extract(DAY FROM start_date))::INTEGER LOOP
    IF (date_part('dow', start_date + i) NOT IN (0,6)) THEN
      result = result + 1;
    END IF;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Answer 3

No function needed, you can do this with a single SQL statement:

SELECT count(*)
FROM generate_series(CURRENT_TIME,
                     date_trunc('month', CURRENT_TIME) + interval '1 month - 1 day',
                     interval '1 day') days(d)
WHERE extract(dow from d) NOT IN (0, 6);

Of course you can wrap this in a SQL function, if so desired. Considering your requirement of remaining work days in the current month, you do not need to specify any parameters.

Answer 4

My answer involves having a table with just a boatload of transactions, at least one per day in the month you care about. Luckily for me, our system allows users to schedule transactions in the future, so I can just ping it for some simple date logic.

SELECT
count(CASE WHEN business_day < date(getdate()) THEN 1 END) as passed
,count(business_day) as total_business_days
FROM
  (SELECT distinct
   date(o.appointment_full_time) as business_day
  FROM
   orders o
  WHERE
   date_trunc('month', o.appointment_full_time) = date_trunc('month', getdate())
--this month
    AND extract(dow from o.appointment_full_time) not in (0,6)
--exclude weekends
    AND date(o.appointment_full_time) 
       not in ('2017-1-1', '2017-1-2', '2017-1-16', '2017-5-29', '2017-7-4', '2017-9-4',
          '2017-11-23', '2017-11-24', '2017-12-25', '2017-12-24', '2017-12-31')
--manually enter the holidays in once per year
 ) a