> > Any thoughts as to the best way to approach this?
>
> Use generate_series:
>
> https://www.postgresql.org/docs/11/functions-srf.html
>
> to generate all the days in the month.
>
> Loop over the days and use EXTRACT:
>
> https://www.postgresql.org/docs/11/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
>
> to find the dates with a dow(The day of the week as Sunday (0) to
> Saturday (6)) or isodow(The day of the week as Monday (1) to Sunday
> (7)) that falls in Mon-Fri and add to counter.
Assumes all weekdays are work days and that all weeks are uniform
for work. Any holiday, emergency, outage leaves you with a skewed
result.
First workaround is table of non-work days: generate a temp table of
the series not intersecting the non-workdays (materialized views
are nice for this).
Then you get into issues of different people having different non-
work days, leaving your subtraction table keyed by person+date.
Frequently the non-work days are by employee class, which allows a
table of days off by employee grade + employees w/ grade => days
off by empoloyee.
Then individual employees will have their own time off due to paid
vacation, medical or family leave, and sick days. Depending on your
number of employees a non_working_days w/ date + employee works or
you get into the pay grade + generic days and employee + pay grade
for the generic days off merged with a separate table of individual
days off. Subtract that from a temp table generated by the sequences
and you'll have a complete schedule.
--
Steven Lembark 3920 10th Ave South
Workhorse Computing Birmingham, AL 35222
lembark@wrkhors.com +1 888 359 3508
