Calculating last work da of the month

[gregwga50]

How can I determine the last workday of the month, assuming workdays are Monday thru Friday. We have our own table that contains the dates of holidays

[B.Hauser]

What about:

Values(Last_Day(YourDate) - Case DayOfWeek_ISO(Last_Day(YourDate))
                                                               When 6 Then 1
                                                               When 7 Then 2
                                                               Else 0
                                                      End Days);

Birgitta

[Vectorspace]

It took me a few passes to understand what that was doing. Clever!

gregwga50 - do you want the last weekday of the month as per Birgitta's example, or the last working day accounting for holidays? If it's the latter, it's hard to provide a complete solution without seeing the structure of your holiday table, but I imagine it could be something like this:

select h.date_of_day from holiday_table h         // Select dates from your holiday table,

 where h.date_of_day >= current date              // Omitting past dates from consideration for efficiency,

   and h.date_of_day < current date + 32 days     // Omitting future dates more than one month away from consideration for efficiency,

   and h.is_working_day = 'Y'                     // That are working days,

   and DayOfWeek_ISO(h.date_of_day) < 6           // And are weekdays (may not need this if is_working_day covers it),

   and month(h.date_of_day) = month(current date) // And are in the same month as current date,

 order by date_of_day desc                        // We want the latest possible date first, as the latest match will be the last working date in the month

 limit 1;                                         // We only want that one date​

The code assumes that your holiday_table is keyed on date_of_day. If you had an index for month(date_of_day) then you wouldn't need the two "omitting xyz dates from consideration" efficiency checks