使用工作日和日历日更新表格 [英] Updating table with business day and calendar day
问题描述
我在 SQL Server 2012 中有一个表，每个月都会手动更新该表，以反映文件预期进入的日期.日期规则已经有值，但预期日期列是手动更新的.如果它预计在 BD1(工作日 1)我将更新到该月的第一个非周末.如果它预计在 CD1(Calenday Day 1)，无论它是在工作日还是周末等，我都会更新到第 1 天.是否可以编写一个更新查询，它会循环遍历值并自动更新?我无法确定更新到正确的工作日.
<上一页>日期规则 |3月预计日期 |4月预计日期 |--------------------------------------------------------|BD1 |2017 年 3 月 1 日 |2017 年 4 月 3 日 ||BD2 |2017 年 3 月 2 日 |||BD3 |2017 年 3 月 3 日 |||BD4 |2017 年 3 月 6 日 |||BD5 ||||BD6 ||||CD1 |2017 年 3 月 1 日 |||CD2 |2017 年 3 月 2 日 |||CD3 |2017 年 3 月 3 日 |||CD4 |2017 年 3 月 4 日 |||CD5 |2017 年 3 月 5 日 |||CD6 |2017 年 3 月 6 日 ||我使用以下代码计算第一个工作日
SELECT DATEADD(DAY,案件(DATEPART(工作日, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) + @@DATEFIRST - 1) % 7当 6 然后 2当 7 然后 1否则 0结尾，DATEADD(月, DATEDIFF(月, 0, GETDATE()), 0))
但是到了第 4 个工作日时，它会给我 2017 年 3 月 4 日(星期六)，而不是 2017 年 3 月 6 日(下星期一).我对如何解决这个问题感到困惑.我认为循环更新查询是最好的
给你.此递归 CTE 将为您提供整个月的 BD:
声明@forwhichdate 日期时间设置@forwhichdate ='20170401'; 与 bd 为 (选择日期添加(天，案件(DATEPART(平日, DATEADD(MONTH, DATEDIFF(MONTH, 0, @forwhichdate), 0)) + @@DATEFIRST - 1) % 7当 6 然后 2当 7 然后 1否则 0结尾，DATEADD(月，DATEDIFF(月，0，@forwhichdate)，0)) 作为 bd，1 作为 n联合所有选择日期添加(天，案件(日期部分(工作日，bd.bd)+@@DATEFIRST - 1)% 7当 5 然后 3当 6 然后 2其他 1结尾，bd.bd) 作为分贝，bd.n+1从 bd where month(bd.bd) = month(@forwhichdate))从 bd 中选择 *结果:
bd n----------- ------------2017-04-03 00:00:00.000 12017-04-04 00:00:00.000 22017-04-05 00:00:00.000 32017-04-06 00:00:00.000 42017-04-07 00:00:00.000 52017-04-10 00:00:00.000 62017-04-11 00:00:00.000 72017-04-12 00:00:00.000 82017-04-13 00:00:00.000 92017-04-14 00:00:00.000 102017-04-17 00:00:00.000 112017-04-18 00:00:00.000 122017-04-19 00:00:00.000 132017-04-20 00:00:00.000 142017-04-21 00:00:00.000 152017-04-24 00:00:00.000 162017-04-25 00:00:00.000 172017-04-26 00:00:00.000 182017-04-27 00:00:00.000 192017-04-28 00:00:00.000 202017-05-01 00:00:00.000 21(21 行受影响)但是，实际上您的查询也应该检查假期.
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)
)
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
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
Result:
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屋！
