每个月的第一个和最后一个工作日的查找 SQL

Question

自1986年以来，我正在尝试找到每个月的第一个和最后一个工作日。使用以下方式，我可以找到任何给定月份的第一天，但仅限于该月，并且不考虑是否为工作日。为了简化起见，目前工作日仅指平日，并不考虑公共假期。

SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

但是我无法得到正确的工作日，所以我创建了一个包含所有工作日的日历表，并想着可以从每个月中提取最小日期，但目前我卡住了。

Date
---------------
1986-01-01
1986-01-02
1986-01-03
1986-01-06
...and so on

我曾尝试获取每个月的第一天，但这并没有考虑到这一天是否为周末。它只是简单地给出了每个月的第一天。

declare @DatFirst date = '20000101', @DatLast date = getdate();
declare @DatFirstOfFirstMonth date = dateadd(day,1-day(@DatFirst),@DatFirst);
select  DatFirstOfMonth = dateadd(month,n,@DatFirstOfFirstMonth)
from    (select  top (datediff(month,@DatFirstOfFirstMonth,@DatLast)+1)
            n=row_number() over (order by (select 1))-1
    from    (values (1),(1),(1),(1),(1),(1),(1),(1)) a (n)
    cross join (values (1),(1),(1),(1),(1),(1),(1),(1)) b (n)
    cross join (values (1),(1),(1),(1),(1),(1),(1),(1)) c (n)
    cross join (values (1),(1),(1),(1),(1),(1),(1),(1)) d (n)
    ) x

我想知道有谁能够为我提供一些关于如何最好地解决这个问题的见解。

- Vince

---

请解释哪一天您认为是工作日？ - Amith Kumar

你们的营业日是哪几天？您是否也考虑公共假期？ - Lonely Planeteer

@AmithKumar 这里定义的工作日是指证券市场交易的日子。现在我们先不用担心公共假日的问题。 - Vince

Answer 1

如果您已经有了包含所有可用日期的日历表，那么您只需要按工作日进行过滤。

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

SELECT
    Year = YEAR(T.Date),
    Month = MONTH(T.Date),
    FirstBusinessDay = MIN(T.Date),
    LastBusinessDay = MAX(T.Date)
FROM
    Calendar AS T
WHERE
    DATEPART(WEEKDAY, T.Date) BETWEEN 1 AND 5 -- 1: Monday, 5: Friday
GROUP BY
    YEAR(T.Date),
    MONTH(T.Date)

您应该使用查询来标记日历表中的这些日期，以便之后可以轻松访问它们。

---

以下是如何使用递归与生成日历表相结合。

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

declare 
    @DatFirst date = '20000101', 
    @DatLast date = getdate();

;WITH AllDays AS
(
    SELECT
        Date = @DatFirst

    UNION ALL

    SELECT
        Date = DATEADD(DAY, 1, D.Date)
    FROM
        AllDays AS D
    WHERE
        D.Date < @DatLast
),
BusinessLimitsByMonth AS
(
    SELECT
        Year = YEAR(T.Date),
        Month = MONTH(T.Date),
        FirstBusinessDay = MIN(T.Date),
        LastBusinessDay = MAX(T.Date)
    FROM
        AllDays AS T
    WHERE
        DATEPART(WEEKDAY, T.Date) BETWEEN 1 AND 5 -- 1: Monday, 5: Friday
    GROUP BY
        YEAR(T.Date),
        MONTH(T.Date)
)
SELECT
    *
FROM
    BusinessLimitsByMonth AS B
ORDER BY
    B.Year,
    B.Month
OPTION
    (MAXRECURSION 0) -- 0: Unlimited

- EzLo

---

非常感谢你提供如此详尽和精彩的解决方案，我真的很感激你的努力和时间 :) - Vince

Answer 2

如果您已经有一个只包含工作日的表格：

select min(datecol), max(datecol)
from BusinessOnlyCalendar
group by year(datecol), month(datecol)

但是您应该扩展您的日历以包含所有可能在日期上进行的计算，例如FirstDayOfWeek/Month/Quarter/Year、WeekNumber等。

当您的日历中有一列指示“工作日是/否”时，这很简单：

select min(datecol), max(datecol)
from calendar
where businessday = 'y'
group by year(datecol), month(datecol)

- dnoeth

---

这很简单明了...没想到会这么容易。谢谢。 - Vince