时间戳

首页 > 解决方案 > SQL 处理用户每周在周表日历上设置的假期

sql-server - SQL 处理用户每周在周表日历上设置的假期

问题描述

假设我有一个叫做 X 的东西，它是根据公式计算的

WorkDaysLeftInTheWeek * X

每周工作日的剩余时间

 WorkDaysLeftInTheWeek = 5 - TotalHolidaysThisWeek - DayIndex

DayIndex 是每个工作日的索引，从 0 开始。

基本上如果没有假期的话

Monday = 0
Tuesday = 1
Wednesday = 2
Thursday = 3
Friday = 4

你为什么问？

我的 X 值为 6。

如果没有假期，那么我的一周有 5 天，这个值必须每天减少，以计算供应给某个生产单位的单位数量。

这是一个例子

周一

  1. 生产单位本周需要 10000 单位
  2. X 是 6
  3. 本周没有用户设置的假期，因此本周工作日为 5
  4. 所以 10000 / ((5 - 0) * 6) = 333,33。这是星期一要供应的单位数量

周二

  1. 生产单位本周需要 10000 单位
  2. X 是 6
  3. 本周没有用户设置的假期，因此本周工作日为 5
  4. 所以 10000 / ((5 - 1) * 6) = 416 这就是周二要供应的单位数量

如果周五一切顺利，这是最后一个工作日，结果将是

10000 / ((5 - 4) * 6) = 1666 个单位。

当然，所需的单位会减少，但这是静态的，只是为了展示它是如何工作的。

那么到目前为止，我做了什么来实现这一目标？

首先我创建了一个表

CREATE TABLE hCalendarios
(
    Id INT PRIMARY KEY,
    Week INT, -- The Week Number
    Day NVARCHAR(20), -- Name of the weekday
    IsHoliday BIT
)

--Insert initial data
INSERT INTO hCalendarios VALUES 
(NULL,'Monday',0),(NULL,'Tuesday',0),(NULL,'Wednesday',0),(NULL,'Thursday',0),(NULL,'Friday',0)

假设用户已经将第 30 周设置为假期 这是表格的结果

Id  Week  Day       IsHoliday
1   30    Monday    0
2   30    Tuesday   0
3   30    Wednesday 1
4   30    Thursday  0
5   30    Friday    0

有了这些数据，我知道哪一天是假期而不保存日期，只需使用工作日并比较工作日的名称

现在这是我的功能

GO
IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'GetDaysLeftCurrentWeek') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION GetDaysLeftCurrentWeek
GO
CREATE FUNCTION GetDaysLeftCurrentWeek (@Days TINYINT)
RETURNS TINYINT
AS BEGIN

    -- Variables
    DECLARE
        @CurrentWeekDay TINYINT, 
        @TotalHolidays TINYINT,
        @IsSameWeek BIT
    -- End Variables

    -- Initialize Variables 
    SET @IsSameWeek = 
        CASE
            WHEN (SELECT TOP 1 1 FROM hCalendarios WHERE Week = DATEPART(week, GETDATE())) = 1 THEN 1
        ELSE 0
        END

    -- Check if holidays are set on current week
    IF(@IsSameWeek = 1)     
    BEGIN
        -- Total holidays on current week
        SET @TotalHolidays = (SELECT COUNT(*) FROM hCalendarios WHERE IsHoliday = 1)
        -- Total work days on the week
        SET @Days = @Days - @TotalHolidays

        -- Get current day of the week
        SET @CurrentWeekDay = (SELECT RowNum
        FROM 
        (
            SELECT *,  ROW_NUMBER() OVER(ORDER BY Id ASC) - 1 AS RowNum FROM hCalendarios
            WHERE IsHoliday = 0 
        ) X
        WHERE Day = (SELECT DATENAME(DW, GETDATE())))

        -- Check if today is a holiday
        IF(@CurrentWeekDay IS NULL)
        BEGIN

            -- Handle when 1st day is holiday
            IF((SELECT DATENAME(DW, GETDATE())) <> 'Monday')        
            BEGIN
                DECLARE @cnt INT
                -- Get the Id of the current day
                SET @cnt = (SELECT Id FROM hCalendarios WHERE Day = (SELECT DATENAME(DW, GETDATE())))

               -- Decrement the Id from the current day until one work day is found
                WHILE NOT EXISTS (SELECT 1 FROM hCalendarios WHERE IsHoliday = 0 AND Id = @cnt)
                BEGIN         
                   SET @cnt = @cnt - 1;
                   IF(@cnt = 1)
                   BEGIN
                       BREAK
                   END
                END;

                -- Decrement by one so 1st work day starts from 0
                SET @CurrentWeekDay = @cnt - 1
            END
            ELSE
            BEGIN 
                -- Set monday to 0 if it's holiday 
                SET @CurrentWeekDay = 0
            END
        END

        RETURN @Days - @CurrentWeekDay
    END

     -- Get current day of the week
    SET @CurrentWeekDay = (SELECT RowNum
    FROM 
    (
        SELECT *,  ROW_NUMBER() OVER(ORDER BY Id ASC) - 1 AS RowNum FROM hCalendarios
        WHERE IsHoliday = 0 
    ) X
    WHERE Day = (SELECT DATENAME(DW, GETDATE())))

    RETURN @Days - @CurrentWeekDay

END

总而言之，这个函数实际上并不需要接收参数，因为它总是使用 5 作为一周中的静态天数

所以步骤是

  1. 检查本周是否有假期。
  2. 如果不只是返回总天数 - 即当前日期的 dayIndex
  3. 如果是同一周，则减去这几天的总假期
  4. 现在要在有假期时获取当前工作日，我必须按行号排序一周的总工作日，因为这将保持相同的 dayIndex 序列，只是天数更少，因为最后是它的最后一个工作日返回 1
  5. 现在首先我处理当天是假期的情况，因为如果是这样，我需要使用前一个工作日的索引。
  6. 我们先处理周一，如果周一是节假日，就返回一周的总工作日数
  7. 现在为了得到最后一个工作日，我使用了一个while循环，基本上如果它到达星期一并且没有找到工作日，它就会打破循环

现在这里有一个问题，如果循环部分在没有任何工作日的情况下到达星期一，则不是 100% 准确。

另外，还有周一和第二天是假期等等的处理，但我不知道如何处理所有这些而不深入研究。

编辑

我将最终结果发布为任何想在类似情况下使用我的功能的人的答案

标签： sql-server

解决方案

我终于做到了！它处理用户标记为假日的每一天或每一天。

如果一周中间有假期并且开始时已经有一个工作日，则使用该工作日的索引。

每当一周以连续 1 天或更多天作为假期开始时，则使用 0

这是最终结果。有了这个，您可以制作一个简单的假期或不活动期间的周历，由用户设置

GO
IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'GetDaysLeftCurrentWeek') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION GetDaysLeftCurrentWeek
GO
CREATE FUNCTION GetDaysLeftCurrentWeek (@Days TINYINT)
RETURNS TINYINT
AS BEGIN

    -- Variables
    DECLARE
        @CurrentWeekDay TINYINT, 
        @TotalHolidays TINYINT,
        @IsSameWeek BIT,
        @WorkDaysLeft TINYINT
    -- End Variables

    -- Initialize Variables 

    -- Total holidays on current week
    SET @TotalHolidays = (SELECT COUNT(*) FROM hCalendarios WHERE IsHoliday = 1)
    -- Total work days on the week
    SET @WorkDaysLeft = @Days - @TotalHolidays

    SET @IsSameWeek = 
        CASE
            WHEN (SELECT TOP 1 1 FROM hCalendarios WHERE Week = DATEPART(week, GETDATE())) = 1 THEN 1
        ELSE 0
    END

    -- Get current day of the week
    SET @CurrentWeekDay = (SELECT RowNum
    FROM 
    (
        SELECT *,  ROW_NUMBER() OVER(ORDER BY Id ASC) - 1 AS RowNum FROM hCalendarios
        WHERE IsHoliday = 0
    ) X
    WHERE Day = (SELECT DATENAME(DW, GETDATE())))


    -- if no holidays in this week, return work days left
    IF(@IsSameWeek = 0)     
    BEGIN    
        RETURN @WorkDaysLeft - @CurrentWeekDay
    END

    -- Get current day of the week
    SET @CurrentWeekDay = 
     -- Filter the data set by current day
        (
            SELECT RowNum
            FROM
            (
                SELECT 
                    *
                FROM 
                (
                    SELECT 
                        *,  
                        ROW_NUMBER() OVER(ORDER BY Id ASC) - 1 AS RowNum FROM hCalendarios
                    WHERE IsHoliday = 0
                ) DerivedTableWorkDaysOnly
                UNION -- Add holidays to the dataset
                SELECT  
                    *,  
                    (
                        -- If week starts with holidays just set them to 0 otherwise get previous highest workday row number
                        SELECT 
                            CASE 
                                WHEN MAX(RowNum) IS NULL THEN 0  
                                ELSE MAX(RowNum) 
                            END 
                        FROM 
                        (
                            -- Get all work days before the current day
                            SELECT 
                                ROW_NUMBER() OVER(ORDER BY Id) - 1 RowNum 
                            FROM hCalendarios Cal1 WHERE IsHoliday = 0  AND Cal1.Id < Cal2.Id 
                        ) DerivedTablePreviousWorkDay
                    ) AS RowNum 
                FROM hCalendarios Cal2
                WHERE IsHoliday = 1

                ) DerivedTableIndexedDays
            WHERE Day = (SELECT DATENAME(DW, GETDATE()))
        )

        RETURN @WorkDaysLeft - @CurrentWeekDay

END

推荐阅读