CODEWITHSUNDEEP
sqlpostgresql
Emil Ahlbäck
Emil Ahlbäck

Reputation: 6206

Taking the first N rows of a table with a condition

This is a contrived example of some scheduling data we process.

The idea is to naively ask the question

Given that a project has N remaining unscheduled hours, a head_count (the number of people working on it), and the assumption that each worker will work 8 days; what non-weekend days AFTER the last scheduled day will this project require?

I have a simplified dim_dates table:

CREATE TABLE dim_dates (
  date_actual DATE NOT NULL,
  is_weekend BOOLEAN NOT NULL
);

And the projects table:

CREATE TABLE projects (
  id SERIAL PRIMARY KEY NOT NULL,
  last_scheduled_date DATE NOT NULL,
  remaining_hours_required INT NOT NULL,
  head_count INTEGER NOT NULL
);

So given this project:

INSERT INTO projects (last_scheduled_date, remaining_hours_required, head_count) 
VALUES ('2018-01-04', 21, 1);

By taking CEIL(remaining_hours_required::decimal / (8 * head_count)) we end up with 3 days of work required. The date 2018-01-04 is a Thursday, so the dates we should end up with is the following Friday, Monday, and Tuesday.

To get the following non-weekend days we can easily join onto dim_dates:

SELECT p.*, d.* 
FROM projects p
INNER JOIN dim_dates d ON (
  d.date_actual > p.last_scheduled_date
  AND
  d.is_weekend IS FALSE
);

Which would give us all following dates that aren't weekends. My conundrum is how to filter this set down to 3 rows.

So the (imperative) task description is:

  1. Calculate the remaining days required to fulfil the project
  2. Take all dim_dates where is_weekend is false and date_actual is above the project's last scheduled date

Here's a fiddle with data: http://sqlfiddle.com/#!17/f6a90/3

Upvotes: 1

Views: 95

Answers (2)

LukStorms
LukStorms

Reputation: 29647

Using a running sum to keep the remaining hours, per project. And keeping those within limit.

In the sub-query it limits on a calculated maximum date.
So that it doesn't calculate for each next date from the dim_dates.

SELECT 
  id as projectId
, date_actual as weekDay
, row_number()
     over (partition by id order by date_actual desc)-1 as daysRemaining
FROM
(
SELECT
 p.id, d.date_actual, p.head_count
, p.last_scheduled_date, p.remaining_hours_required 
,(p.remaining_hours_required 
   - sum(8*head_count) over 
        (partition by p.id order by date_actual)
  ) as remaining_hours
FROM projects p
JOIN dim_dates d 
  ON (d.date_actual > p.last_scheduled_date
  AND d.date_actual <= p.last_scheduled_date + cast(ceil(((p.remaining_hours_required/5.0)*7.0)/(8*p.head_count))+1 as int)
  AND d.is_weekend IS FALSE)
) q
WHERE remaining_hours > (-8*head_count)
ORDER BY id, date_actual;

Test on SQL fiddle here

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

SQL DEMO

WITH dates AS (
  SELECT *, 
  row_number() OVER(ORDER BY date_actual ASC) AS rownum
  FROM dim_dates
  WHERE date_actual > '2018-01-04'::date
  AND is_weekend IS FALSE
), remain_days AS (
    SELECT CEIL (remaining_hours_required::decimal / (8 * head_count))
    FROM projects p
)    
SELECT * 
FROM dates
JOIN remain_days
  ON rownum <= ceil

Upvotes: 1

Related Questions