SQL: Function To find Last business day of the Month factoring in Holidays
-
We all have functions and code for business day calculations, or last day of the month calculations, ect... I am adding a caveat to this for Last business day of the month, not a holiday. I.e. (hypothetical), Last day is 12/31/2015, this is a Friday, however the company give this day off as a holiday so 12/30/2015 needs to be determined as the last business day of the month. I am looking at a couple of ways to approach this. 1) Calculate the Last Day of the month, Determine if it is not a Sat\sun, if is DateDiff to a business day, then take that date and compare to a Holiday table and adjust accordingly. 2) use a function that already exists for Last Business day of the month and then take that date and compare to a Holiday table and adjust accordingly. 3) some better what that I can not think of and no we can not use the XLeratorDB or what ever that is called. The out put I am looking for is just a DATE, the date of the very last company business day of the month.
Thank you In advance! No reason to re-invent the wheel, just improve upon existing.
-
We all have functions and code for business day calculations, or last day of the month calculations, ect... I am adding a caveat to this for Last business day of the month, not a holiday. I.e. (hypothetical), Last day is 12/31/2015, this is a Friday, however the company give this day off as a holiday so 12/30/2015 needs to be determined as the last business day of the month. I am looking at a couple of ways to approach this. 1) Calculate the Last Day of the month, Determine if it is not a Sat\sun, if is DateDiff to a business day, then take that date and compare to a Holiday table and adjust accordingly. 2) use a function that already exists for Last Business day of the month and then take that date and compare to a Holiday table and adjust accordingly. 3) some better what that I can not think of and no we can not use the XLeratorDB or what ever that is called. The out put I am looking for is just a DATE, the date of the very last company business day of the month.
Thank you In advance! No reason to re-invent the wheel, just improve upon existing.
swoozie1 wrote:
use a function that already exists for Last Business day of the month
Maybe somneone already wrote one, but there's no such thing built in, AFAIK. What a "week" or "weekend" is, is determined by your locale, and a company may have multiple of those. Holidays are even more complex, and tend to change rather frequently. Create a loop that inserts weekdays for your locale in a table; that way you have list with the weekends already eliminated. Next, delete every date in there that is mentioned in the holidays table (or join, or make a new table). Select the top result for that month. There's your last theoretical work-day.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
We all have functions and code for business day calculations, or last day of the month calculations, ect... I am adding a caveat to this for Last business day of the month, not a holiday. I.e. (hypothetical), Last day is 12/31/2015, this is a Friday, however the company give this day off as a holiday so 12/30/2015 needs to be determined as the last business day of the month. I am looking at a couple of ways to approach this. 1) Calculate the Last Day of the month, Determine if it is not a Sat\sun, if is DateDiff to a business day, then take that date and compare to a Holiday table and adjust accordingly. 2) use a function that already exists for Last Business day of the month and then take that date and compare to a Holiday table and adjust accordingly. 3) some better what that I can not think of and no we can not use the XLeratorDB or what ever that is called. The out put I am looking for is just a DATE, the date of the very last company business day of the month.
Thank you In advance! No reason to re-invent the wheel, just improve upon existing.
I use a view and a holiday table as I need to keep holidays for a number of countries/currencies. The view starts at the beginning of last year and goes for 10 years, arbitrarily picked by me, it has date part DayOfWeek so I can exclude weekends. It will be fairly simple to combine the two as Eddy suggested, fiddling with the filters/joins to get the last working sate.
Never underestimate the power of human stupidity RAH