Stats Between Two Weekdays (Mon - Fri)

Question

I have the following, stored in a Postgres 9.5.4 database.

CREATE TABLE activity (
    id int primary key,
    cat_id smallint,
    start_date date,
    end_date date
);

INSERT INTO activity VALUES(5822, 1, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5824, 5, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5825, 4, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5840, 5, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5873, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5875, 3, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5876, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5882, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5883, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5888, 4, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5905, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5908, 5, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5911, 5, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5912, 3, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5913, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5915, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5920, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5923, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5928, 5, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5949, 4, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5955, 5, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5959, 4, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5960, 2, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5965, 4, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5998, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6000, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6001, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6003, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6005, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6008, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6012, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6016, 5, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6018, 5, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6032, 4, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6035, 5, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6052, 4, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6060, 4, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6070, 5, '2016-07-13', '2016-07-13');
INSERT INTO activity VALUES(6075, 5, '2016-07-13', '2016-07-11');
INSERT INTO activity VALUES(6076, 3, '2016-07-13', '2016-07-11');
INSERT INTO activity VALUES(6077, 5, '2016-07-13', '2016-07-11');
INSERT INTO activity VALUES(6078, 5, '2016-07-13', '2016-07-13');

I would like to gather a few statistics on the difference between two workdays (Monday to Friday only).

This query might help with what I am trying to achieve.

SELECT 
    SUM(CASE WHEN cat_id = 5 THEN 1 ELSE 0 END)  as "# Cat 5",
    AVG(CASE WHEN cat_id = 5 THEN start_date - end_date END)  as "AVG Cat 5",
    MIN(CASE WHEN cat_id = 5 THEN start_date - end_date END)  as "MIN Cat 5",
    MAX(CASE WHEN cat_id = 5 THEN start_date - end_date END)  as "MAX Cat 5"
FROM activity

I have also created an SQL Fiddle, to help with testing.

To describe this further, suppose an activity is created on Saturday, and completed on Monday, the duration should be 0 number of days (as we are not including Saturday and Sunday). If an activity was created on Friday and completed on Monday, duration for this would be 1.

Thank you.

Can you describe in some more detail what you mean by *statistics on the difference between two workdays*? — Laurenz Albe, Aug 25 '16 at 02:34
I'm looking for average, min and max for the number of days it's taken, for each category. — Glicious, Aug 25 '16 at 02:39
To describe this further, suppose an activity is created on Saturday, and completed on Monday, the duration should be 0 no of days (as we are not including Saturday and Sunday). If an activity was created on Friday and completed on Monday, duration for this would be 1. I hope this helps? Thanks. — Glicious, Aug 25 '16 at 02:50
Yes, it is clearer now. Maybe you should update the question accordingly. — Laurenz Albe, Aug 25 '16 at 02:56

score 1 · Accepted Answer · edited May 23 '17 at 10:33

What you are trying to do here can be done in plain SQL using a CTE. See this answer for calculating the business day a certain number of days away from a date; you need the inverse operation, but you'll get the point.

It is easier and faster, though, to use this function, reworked from the above-linked answer to calculate the number of business days between two dates:

CREATE FUNCTION business_days_between(from_date date, to_date date) RETURNS int AS $$
-- This function assumes Mon-Fri business days. This returns an "open" range of days,
-- i.e. from Monday to Tuesday = 1. For a "closed" range of days (e.g. rentals), you
-- should add 1 to the result.
DECLARE
  alldays   int;   -- All calendar days in the period, counting down to 0
  days      int;   -- The working days to calculate, counting up from 0
  weeks     int;
  start_dow int;
  temp_date date;
BEGIN
  -- If from_date = to_date simply return 0.
  IF from_date = to_date THEN
    RETURN 0;
  END IF;

  -- If dates are reversed, flip them
  IF from_date > to_date THEN
    temp_date := from_date;
    from_date = to_date;
    to_date := temp_date;
  END IF;

  -- Set up variables, remove initial weekend days
  alldays := to_date - from_date;
  start_dow := extract(dow from from_date);
  IF start_dow = 0 THEN -- Don't count initial Sunday
    alldays := alldays - 1;
  END IF;
  IF start_dow = 6 THEN -- Fudge initial Saturday to the following Sunday
    start_dow := 0;
    alldays := alldays - 2;
  END IF;
  IF alldays <= 0 -- from_date is Saturday, to_date is next Sunday or Monday
    RETURN 0;
  END IF;

  -- Process the current week
  IF start_dow + alldays <= 5 THEN -- Stay in this week
    RETURN alldays;
  ELSE                             -- Count towards Friday
    days := 5 - start_dow;
    alldays := alldays - days;
  END IF;

  -- Move by full weeks
  weeks := alldays / 7;
  alldays := alldays - weeks * 7;
  days := days + weeks * 5;

  -- Add any remaining days, after the weekend
  IF alldays > 2 THEN
    days := days + alldays - 2;
  END IF;

  RETURN days;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;

Your query now becomes:

SELECT 
    count(*) as "# Cat 5",
    avg(business_days) AS "AVG Cat 5",
    min(business_days) AS "MIN Cat 5",
    max(business_days) AS "MAX Cat 5"
FROM (
  SELECT business_days_between(start_date, end_date) AS business_days
  FROM activity
  WHERE cat_id = 5) sub;

Stats Between Two Weekdays (Mon - Fri)

1 Answers1