I have a calendar table with few fields out of which below fields are useful and want to add a new column "Promise date" with the below logic to the table. I am struggling with it and need help.

Example: Start Date = '01-02-2019' and then add two days. The result should be a working day.

Table Name := Date Table

Start Date, IsBillingday = 1(Excluding holiday & weekends), WeekdayFlag = Weekday, DayNameOfWeekShort = 'Mon'....Friday.

Started thinking simple first...

CASE WHEN DayNameOfWeekShort = 'Mon' AND ISBillingDay = '1' THEN CONVERT (nvarchar(10) , DATEADD(Day , 2 , dbo.DateTable.StartDate) , 23) 
WHEN DayNameOfWeekShort = 'Tue' AND ISBillingDay = '1' THEN CONVERT (nvarchar(10) , DATEADD(Day , 2 , dbo.DateTable.StartDate) , 23) 
WHEN DayNameOfWeekShort = 'Wed' AND ISBillingDay = '1' THEN CONVERT (nvarchar(10) , DATEADD(Day , 2 , dbo.DateTable.StartDate) , 23) 
WHEN DayNameOfWeekShort = 'Thu' AND ISBillingDay = '1' THEN CONVERT (nvarchar(10) , DATEADD(Day , 4 , dbo.DateTable.StartDate) , 23) 
WHEN DayNameOfWeekShort = 'Fri' AND ISBillingDay = '1' THEN CONVERT (nvarchar(10) , DATEADD(Day , 5 , dbo.DateTable.StartDate) , 23) ELSE '' END

But then the result in some cases is not a working day...How to move to next working day?

For this line

CASE WHEN DayNameOfWeekShort = 'Mon' AND ISBillingDay = '1' THEN CONVERT (nvarchar(10) , DATEADD(Day , 2 , dbo.DateTable.StartDate) , 23)

The result is not a working day...Then need to add +1 day and check whether a working day or not...If not add + 1 ...If yes ..end loop. a

1 Answers

1
Gordon Linoff On

If you want the next working day at least two days in the future, then you can use window functions. I assume you have a flag to identify a working day.

The logic would look like:

select c.*,
       min(case when c.isworkingday = 1 then c.date end) over
           (order by c.date
            rows between 2 following and unbounded following)
           ) as next_workingday_plus_2
from calendar c;

Note that you should be storing dates as dates and not strings, particularly in a calendar table (unless you have one or more columns with dates in a particular format).