c# - 计算连续休假天数跳过节假日和周末

Question

我有一个表，其中记录了用户的休假天数。一个例子是:

+---------+-----------+---------+------------+| country | user_name | user_id |  vac_date  |+---------+-----------+---------+------------+| canada  | James     |    1111 | 2015-02-13 || canada  | James     |    1111 | 2015-02-17 || canada  | James     |    1111 | 2015-02-18 || canada  | James     |    1111 | 2015-02-10 || canada  | James     |    1111 | 2015-02-11 |+---------+-----------+---------+------------+

根据以上数据，从2月13日到2月18日，计数为3，因为14日和15日是周末，16日是加拿大的假期。所以基本上，如果用户在下一个工作日休假，我会尝试保留并继续计数。我还有一张包含所有假期的表格，其中包括国家和假期日期。假期表的示例数据为:

+---------+-------------+-------------+| country | holidayDesc | holidayDate |  +---------+-------------+-------------+| canada  | Family Day  | 2015-02-16  |  +---------+-------------+-------------+

目前我在 SQL 中有一个正常计算日期的查询，所以它只计算休假表中的任何内容。例如:如果用户在 2015 年 3 月 3 日、2015 年 3 月 4 日和 2015 年 3 月 5 日休息，则计数为 3，但对于上表示例，2 月 13 日和 2 的计数仅为 1从2月17日到2月18日。

SELECT DISTINCT user_name    ,min(vac_date) as startDate    ,max(vac_date) as endDate    ,datediff(day, min(vac_date), max(vac_date)) as consecutiveCountFROM (    SELECT user_name        ,vac_date        ,user_id        ,groupDate = DATEADD(DAY, - ROW_NUMBER() OVER (                PARTITION BY user_id ORDER BY vac_date                ), vac_date)    FROM mytable    WHERE country = 'canada'        AND vac_date BETWEEN '20150101'            AND '20151231'    ) zGROUP BY user_name    ,groupDateHAVING datediff(day, min(vac_date), max(vac_date)) >= 0ORDER BY user_name    ,min(vac_date);

这是它当前从上述示例数据中输出的内容:

+-----------+------------+------------+------------------+| user_name | startDate  |  endDate   | consecutiveCount |+-----------+------------+------------+------------------+| James     | 2015-02-10 | 2015-02-11 |                2 || James     | 2015-02-13 | 2015-02-13 |                1 || James     | 2015-02-17 | 2015-02-18 |                2 |+-----------+------------+------------+------------------+

理想情况下我希望它是:

+-----------+------------+------------+------------------+| user_name | startDate  |  endDate   | consecutiveCount |+-----------+------------+------------+------------------+| James     | 2015-02-10 | 2015-02-11 |                2 || James     | 2015-02-13 | 2015-02-18 |                3 |+-----------+------------+------------+------------------+

但我不知道纯 SQL 是否可行。我也可以尝试将其合并到 C# 中。

如果有帮助，我也在使用 C# 和 SQL Server Management Studio。任何帮助，将不胜感激。提前致谢

Answer 1

我尝试走另一条路，但后来找到了 John Cappelletti 解决方案的修复。

首先，您需要将周末日期添加到您的holiday 表中。

Get a list of dates between two dates using a function

然后 UNION ALL 假期和假期，但添加一个描述字段，以便您可以区分两者。

有一些CROSS JOIN所以你可以为每个国家和用户设置假期和周末(需要测试)

SELECT [country], 
       [user_name], [user_id], [vac_date], 'vacation' as description
FROM vacations
UNION ALL 
SELECT c.[country], 
       u.[user_name],
       u.[user_id],
       [holidayDate], 
       'holiday' as description
FROM holidays     
CROSS JOIN (SELECT DISTINCT [country] FROM vacations) c
CROSS JOIN (SELECT DISTINCT [user_name], [user_id] FROM vacations) u  

然后最终查询与 John 所建议的相同，但这次您只计算休假天数。

WITH joinDates as (
    SELECT [country], 
           [user_name], [user_id], [vac_date], 'vacation' as description
    FROM vacations
    UNION ALL 
    SELECT c.[country], 
           u.[user_name],
           u.[user_id],
           [holidayDate], 
           'holiday' as description
    FROM holidays     
    CROSS JOIN (SELECT DISTINCT [country] FROM vacations) c
    CROSS JOIN (SELECT DISTINCT [user_name], [user_id] FROM vacations) u    
)    
Select user_name
      ,startDate = min(vac_date)
      ,endDate   = max(vac_date)
      ,consecutiveCount = count(*)
From  (
        Select *
              ,Grp =  Day(vac_date) - Row_Number() over (Partition By country,user_id 
                                                         Order by vac_date)
         From  joinDates S
      ) A
WHERE description = 'vacation'    -- only count vacation days ignore holiday/weekend   
Group By user_name, Grp
Having count(*)>1
ORDER BY startDate

SQL DEMO

输出

原始输出

这里可以看到分组前的数据