How to assign a same number of business day as Monday to weekend days

Question

I have a date dimension table where i need to add NumberofBusinessDay column which will tell me the number of business day it is. For example:

Date        WeekendFlag  BusinessDayFlag  **NumberofBusinessDay** 
01/01/2015  N            N                0
01/02/2015  N            Y                1
01/03/2015  Y            N                2
01/04/2015  Y            N                2
01/05/2015  N            Y                2

I have assigned the number of business days to the weekdays but I am struggling with assigning a number to a weekend day which will be the same as following business day - Monday (in case Monday is a holiday, it will be Tuesday), as shown in above table. Here it what I have so far:

Date        WeekendFlag  BusinessDayFlag  **NumberofBusinessDay** 
01/01/2015  N            N                0
01/02/2015  N            Y                1
01/03/2015  Y            N                NULL
01/04/2015  Y            N                NULL
01/05/2015  N            Y                2 

Any kind of help would be appreciated

My query for assigning BusinessDays for weekdays (only 2015 data):

SELECT Date, WeekendFlag, BusinessDayFlag
       ,NumberofBusinessDay = CASE WHEN WeekendFlag = 'N' THEN 
       ROW_NUMBER() OVER (Partition BY YearCode, MonthCode ORDER BY WeekendFlag, Date ASC) 
       END
INTO #test
FROM DimDate
WHERE Date between '01/01/2015' AND '12/31/2015' --for testing purposes only
ORDER BY Date ASC

Just an FYI - purpose for accounting weekends as business days is because we don't want to miss any activities that happens during weekend (its a possibility) and we want to count those actitivies in the next business day.

Answer 1

check with below sql

---- below code will crete table for businessday calculation
create table dimension  (calenderdate date,WeekendFlag  char(1) ,BusinessDayFlag   char(1),NumberofBusinessDay int)

declare  @yearstartdate date
declare  @yearenddate date

SELECT   @yearstartdate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 
SELECT   @yearenddate =DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)

while datediff (dd,@yearstartdate,DATEADD(DD,1,@yearenddate)) <>0
begin
 insert into dimension 
 select @yearstartdate ,
 case when datename(DW,@yearstartdate) = 'SUNDAY' or datename(DW,@yearstartdate) = 'SATURDAY'
 then 'Y' else 'N' end,
 case when datename(DW,@yearstartdate) = 'SUNDAY' or datename(DW,@yearstartdate) = 'SATURDAY'
 then 'N' else 'Y' end,
 null

 set @yearstartdate = dateadd(dd,1,@yearstartdate)
end
-----verify table
SELECT * FROM dimension 

-----calculate NumberofBusinessDay number  from below code

;with ctebusinessday
as
(
 select calenderdate,
sum(
case when BusinessDayFlag = 'Y' then 1 else 0 end
   ) over (order by calenderdate) as NOfBusinessDay
   from dimension 
)
update b
set b.NumberofBusinessDay =c.NOfBusinessDay
from dimension  b join ctebusinessday c
on b.calenderdate = c.calenderdate
  
  
update dimension 
set NumberofBusinessDay = NumberofBusinessDay + 1
where weekendflag = 'Y'
--verify table
select * from dimension

you can update any date as businessday or weekend day and calculate NumberofBusinessDay from NumberofBusinessDay calculation code part . i marked only saturaday as weekend day and populated table

Thanks

Answer 2

In SQL Server 2012+, you can do this with a cumulative sum:

select dd.*,
       sum(case when BusinessDayFlag = 'Y' then 1 else 0 end) over (order by date) as NumberOfBusinessDay
from DateDimension;

You can put this in an update as:

with toupdate as (
      select dd.*,
             sum(case when BusinessDayFlag = 'Y' then 1 else 0 end) over (order by date) as newNumberOfBusinessDay
      from DateDimension
     )
update toupdate
    set NumberOfBusinessDay = newNumberOfBusinessDay;

In earlier versions of SQL Server, you can do something similar using cross apply.

EDIT:

Based on your sample data, your seem to be counting the number of weekdays, not business days. If so, the above is just using the wrong variable:

with toupdate as (
      select dd.*,
             sum(case when WeekEndFlag = 'N' then 1 else 0 end) over (order by date) as newNumberOfBusinessDay
      from DateDimension
     )
update toupdate
    set NumberOfBusinessDay = newNumberOfBusinessDay;

This is based on your sample results. Note that this is probably off-by-1, so you should subtract 1. From the description, though, I don' understand why the first row has a value of 0 and not 1.