How to make SSRS subscriptions Kick off on Business Day 3 of month-CodePudding

I am trying to figure out how to use the Data-Driven Subscription portion of SSRS to fire out a report to a bunch of people via email on the third business day of the month.

I'm a bit new to SQL but am learning very quickly, this just happens to be out of my small realm of knowledge.

I do have a table full of days of months, what year, what month, days of the week and all of that in different date formats. I just can't figure it out, I feel it's within my grasp of understanding though.

So far this is what I have and I feel like this could be summarized into a different easier sql statement? More optimized I guess.

select distinct --(CASE --when day_of_week = (2,3,4,5,6) then dateadd(day,1,day_desc_01) --when day_of_week = (7) then dateadd(day,2,day_desc_01) else day_of_week end) as 'BD_Date' day_of_week , day_desc_01 , date from Company.dbo.Company_Calendar where year = 2023 and day_of_week not in (1,7) and date <> '1900-01-01' and day_weekday_ct = 1 and year = 2023

I just want it to return the 3rd business day of the month for every month. Then probably a statement that says if it is the 3rd business day, fire off the report, if not, do nothing. I hope this makes a little bit of sense? I could also be way off track on this and way in over my head.

Thank you for your time and help!

CodePudding user response：

This is a generic exmaple, but you should be able to apply the logic to your company calendar table.

This query will simply give you all the 3rd business days in the month. It filters out weekends and public holidays (assuming you have some way of recording the public holidays in your table). Once the filters are applied, if just assigns a row number to each records within in each calendar year/month and the only returns those where it is 3.

There are some extra columns included for clarity.

You may be able to simplify this but I dont; know what your calendar table contains so this assumes it has just the date (TheDate) and a column to indicate PublicHoliday.

CREATE VIEW ThirdBusDay AS
SELECT 
    * 
    FROM (
        SELECT 
              TheDate
            , DayOfWeek = DATEPART(WeekDay, TheDate)
            , TheYear = YEAR(TheDate)
            , TheMonth = MONTH(TheDate)
            , TheDay = DAY(TheDate)
            , WorkingDayOfMonth = ROW_NUMBER() OVER(PARTITION BY YEAR(TheDate), MONTH(TheDate) ORDER BY TheDate)
        FROM myCalendarTable
        WHERE 
            DATEPART(WeekDay, TheDate) NOT IN (7,1) -- Filter out Saturday and Sunday, change this if system week start is not Sunday.
            AND 
            PublicHoliday = 0 -- filter out public holidays etc..
        ) d 
    WHERE WorkingDayOfMonth = 3

So if CAST(GetDate() AS Date) exists in this view then you know you have to execute the report.