> > 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@xxxxxxxxxxx +1 888 359 3508
