I have tried several versions of code to determine different business days of the month. I have a need for code to execute on the 6th and the 10th business day of the month. Holidays must be taken into consideration as well. My code to determine the 6th day does not work for determining the 10th day and it is making me crazy. Could someone take a look at it? When I try what I think is the right value for @days in the case stmt, I get everyday but the one I am looking for. For June the 10th business day is the 14th; for July it's the 16th.
Declare @days int , @firstofMon varchar(11) -- Get first of current Month Set @firstofMon = Convert(Varchar(11),DATEADD(month, DATEDIFF(month, 0, Getdate()), 0),110) -- If Month is Jan, July, or Sept build in extra day for holiday business day count is 7 -- else 1st of the month is weekend then business day count is 6, else business day count is 5. Set @days = Case When DatePart(MONTH,@firstofMon) IN (1,7,9) and DATEPART(WEEKDAY,(Convert(Varchar(11),DATEADD(month, DATEDIFF(month, 0, @firstofMon), 0),110))) = 7 --first of month is Sat with holiday Then 8 When DatePart(MONTH,@firstofMon) IN (1,7,9) and DATEPART(WEEKDAY,(Convert(Varchar(11),DATEADD(month, DATEDIFF(month, 0, @firstofMon), 0),110))) = 1 --first of month is Sun with holiday Then 7 When DatePart(MONTH,@firstofMon) IN (1,7,9) and DATEPART(WEEKDAY,(Convert(Varchar(11),DATEADD(month, DATEDIFF(month, 0, @firstofMon), 0),110))) NOT IN (1,7) --first of month is WeekDay with holiday Then 6 When DatePart(MONTH,@firstofMon) NOT IN (1,7,9) and DATEPART(WEEKDAY,(Convert(Varchar(11),DATEADD(month, DATEDIFF(month, 0, @firstofMon), 0),110)))IN (1,7) --first of month is WeekDay no holiday Then 6 -- normal business day calc Else 5 -- altered busniess day calc End Select DATEADD(day, (@days % 5) + CASE ((@@DATEFIRST + DATEPART(weekday, @firstofMon) + (@days % 5)) % 7) WHEN 0 THEN 2 WHEN 1 THEN 1 ELSE 0 END, DATEADD(week, (@days / 5), @firstofMon))
Answer by WRBI ·
As both Kev and I have mentioned the best way to approach this would to be adding a calendar to a database (it doesnt have to solely be a DW). In previous organisations that I've worked in we're added them to maintenance/custom log databases that are used to monitor the instances. Calendar tables come in use for loads of queries not just this one - so you'd probably use it time and again.
If that's not an option:
I've not tested this code or than in SQL Fiddle, so have a play around with it and make sure it works the way that you want it to. I'd suggest adding into a SPROC and then if it needs changing you can alter it there rather than doing in SQL Agent.
** This is dev code..... You will want to make changes to this! Put it into temp tables/table variables. Maybe get rid of the WHILE loop and format it correctly. etc. **
-- Declare the input parameter DECLARE @PassedInParameter tinyint = 10 -- 6 -- Get dates based on today. -- For testing comment out the dateadd and feed in the hard coded value after it DECLARE @FirstDayOfThisMonth DATE = DATEADD(month, DATEDIFF(month, 0, Getdate()), 0) --'2018-07-01' DECLARE @FirstDayOfNextMonth DATE = DATEADD(month,1,@FirstDayOfThisMonth) -- Create a temp table CREATE TABLE Calendar_Temp ( CalendarDate DATE, IsHoliday BIT, WeekdayFlag BIT, WorkingDayOfMonth TINYINT ) -- Declare a variable for the loop DECLARE @LoopDate DATE = @FirstDayOfThisMonth -- Lets build a calendar -- It calculates whether its a weekday or weekend WHILE @LoopDate < @FirstDayOfNextMonth BEGIN INSERT INTO Calendar_Temp (CalendarDate, IsHoliday, WeekdayFlag) SELECT @LoopDate, CASE WHEN DATEPART(MONTH, @FirstDayOfThisMonth) IN (1,7,9) THEN 1 ELSE 0 END IsHoliday , CASE WHEN DATENAME(WEEKDAY, @loopdate) IN ('Saturday','Sunday') THEN 0 ELSE 1 END AS WeekdayFlag -- Increase the loop we dont want to go infinite SET @LoopDate = DATEADD(day,1,@loopdate) END -- Use row number to number the working days. UPDATE c set WorkingDayOfMonth = x.RowNumber FROM Calendar_Temp as c INNER JOIN ( SELECT CALENDARDATE, row_number() OVER(ORDER BY CalendarDate asc) AS RowNumber FROM CALENDAR_TEMP where weekdayflag = 1 ) AS X ON C.CALENDARDATE = X.CALENDARDATE -- Now lets work out if the holiday impacts the working day, if it does add 1 to it to get the next working day. set @PassedInParameter = ( select case when IsHoliday = 1 then @PassedInParameter + 1 else @PassedInParameter end from calendar_temp where WorkingDayOfMonth = 1 ) -- now get the date.... if in a sproc this is what you return in an output. select calendardate from calendar_temp where WorkingDayOfMonth = @PassedInParameter
Answer by Kev Riley ·
Having done things like this in the past, I can say from experience that it is far easier to have a calendar table that represents all dates and have a flag to indicate which are business days and which are not, rather than trying to build some logic that tries to figure it out.
Answer by Jon Crawford ·
Everyone is pointing you in the right direction, if you can't put this in a permanent table, then you should include a CTE that defines your holidays before you define a temp table with the working days. holidays change in different companies, different countries, etc. For example, here in the U.S. we celebrate the fourth of July as the day we shrugged off the oppressive burden of our imperialist overlords. Something about putting tacks in our tea, we're not big on history. Somehow I doubt our peers in London celebrate the same.
Since you can't count on the holidays being the same everywhere, and since not every holiday is tied to a date (some are the first or third of a particular weekday in the month, etc.) then you have to define it up front.