在SQL中计算给定月份的第一个工作日

Question

我必须计算每月前“N”天内已支付的所有发票。 我有两张桌子

。 INVOICE：有发票信息。 唯一重要的字段称为“datePayment”

。 HOLYDAYS：这是一个单列表。 该表中的条目的格式为“2009-01-01”， 2009-05-01”等等。

我还应该考虑周六和周日 （这可能不是问题，因为我可以在假日表中插入那些日子，以便在必要时将它们视为假日）

问题是计算哪个是“付款限额”。

select count(*) from invoice 
where datePayment  < PAYMENTLIMIT

我的问题是如何计算这个 PAYMENTLIMIT。 其中 PAYMENTLIMIT 是“每月的第五个工作日”。

查询应在 Mysql 和 Oracle 下运行，因此应使用标准 SQL。

有什么提示吗？

编辑 为了与问题标题保持一致，伪查询应如下所示：

select count(*) from invoice 
where datePayment  < FIRST_WORKING_DAY + N

然后可以将问题简化为计算每个月的 FIRST_WORKING_DAY 。

Answer 1

返回当月的第一个星期一

SELECT DATEADD(
    WEEK,
    DATEDIFF( --x weeks between 1900-01-01 (Monday) and inner result
        WEEK,
        0, --1900-01-01
        DATEADD( --inner result
            DAY,
            6 - DATEPART(DAY, GETDATE()),
            GETDATE()
        )
    ),
    0 --1900-01-01 (Monday)
)

Answer 2

SELECT DATEADD(day, DATEDIFF (day, 0, DATEADD (month, DATEDIFF (month, 0, GETDATE()), 0)  -1)/7*7 + 7, 0);

Answer 3

select if(weekday('yyyy-mm-01') < 5,'yyyy-mm-01',if(weekday('yyyy-mm-02') < 5,'yyyy-mm-02','yyyy-mm-03'))

周六和周日是 5 点、6 点，因此您只需要两次检查即可获得第一个工作日

Answer 4

您可以查找一个月中的第一个日期，该日期不在假期表中并且该日期不是周末：

select min(datePayment), datepart(mm, datePayment)
from invoices
where datepart(dw, datePayment) not in (1,7) --day of week
and not exists (select holiday from holidays where holiday = datePayment)
group by datepart(mm, datePayment) --monthnr

Answer 5

像这样的事情可能会起作用：

create function dbo.GetFirstWorkdayOfMonth(@Year INT, @Month INT)
returns DATETIME
as begin
    declare @firstOfMonth VARCHAR(20)
    SET @firstOfMonth = CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR) + '-01'

    declare @currDate DATETIME 
    set @currDate = CAST(@firstOfMonth as DATETIME)

    declare @weekday INT
    set @weekday = DATEPART(weekday, @currdate)

    -- 7 = saturday, 1 = sunday
    while @weekday = 1 OR @weekday = 7
    begin
        set @currDate = DATEADD(DAY, 1, @currDate)
        set @weekday = DATEPART(weekday, @currdate)
    end

    return @currdate
end

我不能 100% 确定“工作日”数字是固定的还是可能取决于 SQL Server 上的区域设置。 一探究竟！

马克

Answer 6

我们没有使用要排除的假期表，而是使用日历表方法：一行代表应用程序需要的每一天（三十年跨越了适度的 11K 行）。 因此，它不仅具有 is_weekday 列，还具有与企业相关的其他内容，例如 julianized_date。 这样，每个可能的日期都会有一个现成的 first_working_day_this_month 值，并且查找它需要一个简单的查找（SQL 产品往往会针对该值进行优化！），而不是每次都“计算”它飞。

Answer 7

我们的应用程序中有日期表（填充了数十年的所有日期和日期部分），它允许各种“缺失”日期操作，例如（在伪sql中）：

select min(ourdates.datevalue)
from ourdates
where ourdates.year=<given year> and ourdates.month=<given month>
    and ourdates.isworkday
    and not exists (
        select * from holidays
        where holidays.datevalue=ourdates.datevalue
    )

Answer 8

好的，第一次尝试时，您可以将以下代码放入 UDF 中，并将年份和月份作为变量传递。 然后它可以返回 TestDate，这是该月的第一个工作日。

DECLARE @Month INT
DECLARE @Year INT

SELECT @Month = 5
SELECT @Year = 2009

DECLARE @FirstDate DATETIME
SELECT @FirstDate = CONVERT(varchar(4), @Year) + '-' + CONVERT(varchar(2), @Month) + '-' + '01 00:00:00.000'

DROP TABLE #HOLIDAYS
CREATE TABLE #HOLIDAYS (HOLIDAY DateTime)

INSERT INTO #HOLIDAYS VALUES('2009-01-01 00:00:00.000')
INSERT INTO #HOLIDAYS VALUES('2009-05-01 00:00:00.000')

DECLARE @DateFound BIT
SELECT @DateFound = 0
WHILE(@DateFound = 0)
BEGIN
    IF(
        DATEPART(dw, @FirstDate) = 1
        OR
        DATEPART(dw, @FirstDate) = 1
        OR
        EXISTS(SELECT * FROM #HOLIDAYS WHERE HOLIDAY = @FirstDate)
    )
    BEGIN
        SET @FirstDate = DATEADD(dd, 1, @FirstDate)
    END
    ELSE
    BEGIN
        SET @DateFound = 1
    END
END

SELECT @FirstDate

但我不喜欢这个解决方案的是，如果您的假期表包含该月的所有天，则会出现无限循环。 （您可以检查循环是否仍在查看正确的月份）它依赖于相同的日期，例如全部在时间 00:00:00。 最后，我使用字符串连接计算过去一个月的第一天的方法是一个捷径。 有很多更好的方法可以找到该月的实际第一天。

Answer 9

获取 2009 年每个月的前 N ​​个工作日：

select * from invoices as x
where 
    datePayment between '2009-01-01' and '2009-12-31'


    and exists
    ( 
        select             
                 1
        from invoices
        where
            -- exclude holidays and sunday saturday...
            (
                datepart(dw, datePayment) not in (1,7) -- day of week


                /*
                -- Postgresql and Oracle have programmer-friendly IN clause
                and 
                (datepart(yyyy,datePayment), datepart(mm,datePayment))
                not in (select hyear, hday from holidays) 
                */


                -- this is the MSSQL equivalent of programmer-friendly IN
                and 
                not exists
                (
                    select * from holidays
                    where 
                        hyear = datepart(yyyy,datePayment)
                        and hmonth = datepart(mm, datePayment)
                )                                
            )
            -- ...exclude holidays and sunday saturday



            -- get the month of x datePayment
            and                 
            (datepart(yyyy, datePayment) = datepart(yyyy, x.datePayment)
             and datepart(mm, datePayment) = datepart(mm, x.datePayment)) 


        group by 
            datepart(yyyy, datePayment), datepart(mm, datePayment)    

        having 
            x.datePayment < MIN(datePayment) + @N -- up to N working days
    )