与工作日和日历日更新表 [英] Updating table with business day and calendar day

问题描述

我在SQL Server 2012中有一个表，该表每个月手动更新以反映文件预期输入的日期.日期规则已具有值，但预期日期列是手动更新的内容.如果预计在BD1(第1个工作日)进行，则我将更新至该月的第一个非周末.如果它预期在CD1(日历第1天)上出现，我将更新为第一天，而不管它是在工作日还是在周末等等.是否可以编写一个更新查询，使其在值之间循环并自动更新?我无法确定要更新到正确的工作日.

I have a table in SQL Server 2012 that is updated manually every month to reflect what date is a file expected to come in. The date rule already has values but the expected date column is what is updated manually. If its expected on BD1(Business Day 1) I will update to the first non-weekend day of the month. If its expected on CD1(Calenday Day 1) I will update to the 1st regardless if it falls on a weekday or a weekend and so forth. Is it possible to write an update query where it would loop through the values and update automatically? I'm having trouble figuring out to update to the correct business day.

date rule  | March expected date | April expected date |
--------------------------------------------------------
| BD1      | 3/1/2017            | 4/3/2017            |
| BD2      | 3/2/2017            |                     |
| BD3      | 3/3/2017            |                     |
| BD4      | 3/6/2017            |                     |
| BD5      |                     |                     |
| BD6      |                     |                     |
| CD1      | 3/1/2017            |                     |
| CD2      | 3/2/2017            |                     |
| CD3      | 3/3/2017            |                     |
| CD4      | 3/4/2017            |                     |
| CD5      | 3/5/2017            |                     |
| CD6      | 3/6/2017            |                     |

我正在使用以下代码来计算第一个工作日

I was using the following code to calculate the first business day

SELECT DATEADD(DAY,
CASE
    (DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) + @@DATEFIRST - 1) % 7
    WHEN 6 THEN 2 
    WHEN 7 THEN 1
    ELSE 0
END,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

)

但是到第4个工作日时，它将给我3/4/2017(星期六)，而不是下一个星期一的3/6/2017.我在如何解决这个问题上陷入了困境.我认为最好使用循环更新查询

but then when it would come to business day 4, it would give me 3/4/2017 which is a saturday instead of 3/6/2017 which is the following monday. i'm getting stumped in how to tackle this. I'm thinking a loop update query would be best

推荐答案

在这里.此递归CTE将为您提供整个月的BD:

here you go. This recursive CTE will give you the BDs for the whole month:

declare @forwhichdate datetime
set @forwhichdate ='20170401'
;with bd as(
select 
DATEADD(DAY,
CASE
    (DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @forwhichdate), 0)) + @@DATEFIRST - 1) % 7
    WHEN 6 THEN 2 
    WHEN 7 THEN 1
    ELSE 0
END,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @forwhichdate), 0)
) as bd, 1 as n
UNION ALL
SELECT DATEADD(DAY,
CASE
    (DATEPART(WEEKDAY, bd.bd) + @@DATEFIRST - 1) % 7
    WHEN 5 THEN 3
    WHEN 6 THEN 2
    ELSE 1
END,
bd.bd
) as db, 
bd.n+1
from bd where month(bd.bd) = month(@forwhichdate)
)
select * from bd

结果:

bd                      n
----------------------- -----------
2017-04-03 00:00:00.000 1
2017-04-04 00:00:00.000 2
2017-04-05 00:00:00.000 3
2017-04-06 00:00:00.000 4
2017-04-07 00:00:00.000 5
2017-04-10 00:00:00.000 6
2017-04-11 00:00:00.000 7
2017-04-12 00:00:00.000 8
2017-04-13 00:00:00.000 9
2017-04-14 00:00:00.000 10
2017-04-17 00:00:00.000 11
2017-04-18 00:00:00.000 12
2017-04-19 00:00:00.000 13
2017-04-20 00:00:00.000 14
2017-04-21 00:00:00.000 15
2017-04-24 00:00:00.000 16
2017-04-25 00:00:00.000 17
2017-04-26 00:00:00.000 18
2017-04-27 00:00:00.000 19
2017-04-28 00:00:00.000 20
2017-05-01 00:00:00.000 21

(21 row(s) affected)

但是，实际上，您的查询还应该检查假期.

However, in reality your query should also check for the holidays.

这篇关于与工作日和日历日更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！