Updating table with business day and calendar day

问题:

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

回答1:

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.

回答2:

you can use this logic.. where will find the next business day skipping the weekends.

declare @datetoday date = '2017-04-01'


select iif(datepart(dw,@datetoday)=1, dateadd(day,1,@datetoday),iif(datepart(dw,@datetoday)=7,dateadd(day,2,@datetoday),@datetoday))

回答3:

A calendar/tally table would do the trick, but we can use an ad-hod tally table.

Declare @Date1 date = '2017-03-01'
Declare @Date2 date = EOMonth(@Date1)

Select *
 From (
        Select Date    = D
              ,WeekDay = DateName(WEEKDAY,D)
              ,DayCode = concat('BD',Row_Number() over(Order By D))
         From (Select Top (DateDiff(DD,@Date1,@Date2)+1) D=DateAdd(DD,-1+Row_Number() Over (Order By Number),@Date1) From  master..spt_values) A
         Where DateName(WEEKDAY,D) Not in ('Saturday','Sunday')
        Union All
        Select Date    = D
              ,WeekDay = DateName(WEEKDAY,D)
              ,DayCode = concat('CD',Row_Number() over(Order By D))
         From (Select Top (DateDiff(DD,@Date1,@Date2)+1) D=DateAdd(DD,-1+Row_Number() Over (Order By Number),@Date1) From  master..spt_values) A
      ) A
 Where substring(DayCode,3,2)<=6

Returns

Date        WeekDay     DayCode
2017-03-01  Wednesday   BD1
2017-03-02  Thursday    BD2
2017-03-03  Friday      BD3
2017-03-06  Monday      BD4  --< Notice BD4 is Monday
2017-03-07  Tuesday     BD5
2017-03-08  Wednesday   BD6
2017-03-01  Wednesday   CD1
2017-03-02  Thursday    CD2
2017-03-03  Friday      CD3
2017-03-04  Saturday    CD4
2017-03-05  Sunday      CD5
2017-03-06  Monday      CD6

回答4:

IMHO , your table structure is wrong in two way, i) daterule column should be split in 2 columns like in my CTE.Then calculation will be far easier.

ii) Secondly you don't keep your column in such pivoted manner.this it will keep on increasing.So hope your handling your requirement in correct way.

Keeping your current structure in mind,

declare @t table(daterule varchar(20),Marchexpected date , Aprilexpected date )
insert into @t VALUES
('BD1','3/1/2017', '4/3/2017')
,('BD2','3/2/2017',  null       )
,('BD3','3/3/2017',    null  )
,('BD4','3/4/2017',    null  )
,('BD5',  null      ,  null  )
,('BD6',  null      ,  null  )
,('CD1','3/1/2017',    null  )
,('CD2','3/2/2017',    null  )
,('CD3','3/3/2017',    null  )
,('CD4','3/4/2017',    null  )
,('CD5','3/5/2017',    null  )
,('CD6','3/6/2017',    null  )
declare @AprilDate date='2017-04-01'
;With CTE as
(
SELECT *
,case when daterule like 'BD%' then 'BD' else 'CD' end ruletype
,cast(replace(replace(daterule,'BD',  '' ),'CD','') as int) ruletypeid
 from @t
)
select daterule,Marchexpected
,case when datename(dw, dateadd(day,(ruletypeid-1),@AprilDate))='Saturday'
then dateadd(day,(ruletypeid+1),@AprilDate)
when datename(dw, dateadd(day,(ruletypeid-1),@AprilDate))='Sunday'
then dateadd(day,(ruletypeid),@AprilDate)
else
dateadd(day,(ruletypeid-1),@AprilDate)
END
Aprilexpected
from cte
where   ruletype='BD'

union ALL


select daterule,Marchexpected
,dateadd(day,(ruletypeid-1),@AprilDate)
from cte
where   ruletype='CD'