SQL Server How to find a specific business date

ccgok5k5  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(103)

I have a client requesting an automated daily report that includes several month to date metrics. Normally I would use code like this to find start and end of month values:

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)  AS 'First calendar date of current month'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))  AS 'Last calendar date of current month'

which would produce a result set of:

First calendar date of current month
2023-09-01 00:00:00.000

Last calendar date of current month
2023-09-30 23:59:59.000

The problem I have is that this client does not use the standard calendar month. Their calendar starts on the 2nd to last business day of the prior month and ends on the 3rd to last business day of the current month. Using this month as an example, the date range would be '2023-08-30 00:00:00.000' - '2023-09-27 23:59:59.000'. Everything I have read on the topic talks about counting business days in a month with DATEPART, when I need actual datetime values. Does anyone know if there is a way to achieve this?

5rgfhyps

5rgfhyps1#

As the others have commented: It's recommended to create a calendar table. At minimum, you will need to know your Holidays and/or closure dates. I would turn this into two separate functions but below I'll show you as a single query.

Also, consider evaluating if there are slight changes for January. Example - If you run this on 1-15-2023, will they want the start date to run into the prior year?

  1. The key is to create a CTE to generate dates.
  2. Then another CTE that references the first to filter out your Holidays.
  3. Return the TOP 2 or TOP 3 while filtering out Weekdays. The built-in DATEPART() function, using DW as your datepart, will give you an integer for the week; 1 = Sunday 7 = Saturday.

Example:

DECLARE @startDate DATE = GETDATE();
DECLARE @firstBusinessDay DATE;
DECLARE @lastBusinessDay DATE;

--Add Holidays If needed, Highly recommended that this become an actual table that is managed
DECLARE @Holidays TABLE (
    HolidayDate Date
)
--Holiday sample dates
INSERT INTO @Holidays VALUES 
    ('2023-01-02'),
    ('2023-04-07'),
    ('2023-05-29'),
    ('2023-07-04'),
    ('2023-09-04'),
    ('2023-11-23'),
    ('2023-11-24'),
    ('2023-12-25'),
    ('2023-12-26')
;WITH CTE
AS
(
    SELECT 
        EOMONTH(@startDate,-1) AS [day], --Anchor for last day of prior month
        1 as [ct]
    UNION ALL --Union between Anchor member and Rescursive members
    SELECT 
        DATEADD(DAY,-1,[day]), --Add each prior date
        [ct]+1 --We use this to stop the recursion in the WHERE clause
    FROM CTE  --Reference the current CTE to create an recursion
    WHERE [ct] < 5 --We will never need more than 5 dates to work with, so we stop the recursion
), CTE_WEEKDAYS
AS
(
    SELECT TOP 2 [day] FROM CTE WHERE DATEPART(DW,[day]) NOT IN (1,7) --Only days that are weekdays
)
SELECT TOP 1 @firstBusinessDay = [day] FROM CTE_WEEKDAYS WHERE [day] NOT IN (SELECT HolidayDate FROM @Holidays) ORDER BY [day] ASC  --2nd to last calendar day of the prior month, ignoring Holidays
;WITH CTE
AS
(
    SELECT 
        EOMONTH(@startDate) AS [day], --Anchor for last day of prior month
        1 as [ct]
    UNION ALL --Union between Anchor membe and Rescursive members
    SELECT 
        DATEADD(DAY,-1,[day]), --Add each prior date
        [ct]+1 --We use this to stop the recursion in the WHERE clause
    FROM CTE WHERE [ct] < 5 --We will never need more than 5 dates to work with, so we stop the recursion
), CTE_WEEKDAYS
AS
(
    SELECT TOP 3 [day] FROM CTE WHERE DATEPART(DW,[day]) NOT IN (1,7) --Only days that are weekdays
)
SELECT TOP 1 @lastBusinessDay = [day] FROM CTE_WEEKDAYS WHERE [day] NOT IN (SELECT HolidayDate FROM @Holidays) ORDER BY [day] ASC --3nd to last calendar day of the current month, ignoring Holidays
SELECT @firstBusinessDay, @lastBusinessDay --Start and End days for the period

相关问题