Calculate number of workdays PER MONTH from start_date and end

So I have a table that looks like this :

task_id | start_date |end_date

I want to calculate the number of workdays (just days from mondays to fridays , no holidays) per month.

for example : if a task took from 02-01-2022 to 05-02-2022 to be accomplished, i need the result to look something like this

task_id | january |february |march |april .............|december
1           21        4       0       0 ..........         0

CodePudding user response：

You can try to use generate_series function to generate date during your start_date and end_date which we can easy to count then the condition aggregate function to make pivot.

extract can get the month number or workdays(from Mondays to Fridays) by TIMESTAMP type, we can use that be count condition in aggregate function.

SELECT t1.task_id,
   count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 1 THEN 1 END) january,
   count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 2 THEN 1 END) february,
   count(CASE WHEN extract(isodow from dt) BETWEEN 1 AND 5 AND EXTRACT(MONTH from dt) = 3 THEN 1 END) march
    -- more months you can write
FROM T t1
CROSS JOIN generate_series(t1.start_date,t1.end_date,'1 day'::interval) dt
group by t1.task_id

sqlfiddle