Click here to Skip to main content
14,934,046 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Team,

*I have a holiday list in Sql Table.
*I have 2 services 1 and 2.
*For service 1 need to add 30 days for the created date.
*For service 2 need to add 5 days for the created date.

Now I need to skip the holiday dates if the dates comes in between and need to show the next working date. Kindly help on this. Thanks in Advance.

What I have tried:

SQL Table


Date_Fmt	Holiday_Name
2020-10-10	Basava Jayanthi
2020-10-12	Krishna Jayanthi
2020-10-15	Mahaveer Jayanthi
2020-10-18	Dipavali
2020-10-22	Dasara


tried few quires not work out
Posted
Updated 7-Oct-20 21:50pm
Comments
Richard MacCutchan 8-Oct-20 3:41am
   
Please use the Improve question link above and add the code you have tried, and explain what happens when you run it.
Richard Deeming 8-Oct-20 4:00am
   
Are you wanting to move the final date if it lands on a holiday date, or are you wanting to add 5 non-holiday days to the starting date?

Eg: If the starting date is 2020-10-07, adding five days will give you 2020-10-12. Do you want to move that to 2020-10-13, since it lands directly on Krishna Jayanthi, ignoring the fact that those five days also include Basava Jayanthi? Or do you want to take both holidays into account, and move the result to 2020-10-14 instead?
KriShna RaJendra N PraSad 8-Oct-20 5:58am
   
Yes it should not move if the holiday is on 2020-10-13. It should give next working date. the date which is not in the holiday list. Thanks in advance.

1 solution

Assuming your table name is TBL_Holidays and saturday, sunday are non-working days
You can try this to get the working date:
DECLARE @NoOfWorkingDays INT;
SET @NoOfWorkingDays = 5;

DECLARE @StartDate DATE
SET @StartDate = '20201001'
DECLARE @CurrDate datetime;
DECLARE @Counter INT; 
DECLARE @DayAdd INT; 
SET @Counter=0;
SET @DayAdd =1;
WHILE (@Counter < @NoOfWorkingDays)
BEGIN
	SET @CurrDate = DATEADD(DAY,@DayAdd,@StartDate);
	If DATENAME(dw, @CurrDate) NOT IN ('Saturday', 'Sunday')
	   AND (@CurrDate NOT IN (SELECT Date_FMT FROM TBL_Holidays))
		SET @Counter  = @Counter  + 1;
	SET @DayAdd  = @DayAdd  + 1;
END
SELECT @CurrDate;
   
v2
Comments
Tan Chee Yong 8-Oct-20 6:09am
   
I will assume the 30 days and 5 days are working days. If they are calendar days, do a dateadd function and check whether it is a working day and not in holiday table, if not valid, do a looping by adding 1 day each time to check until it is a working day
KriShna RaJendra N PraSad 8-Oct-20 6:22am
   
Thanks For your replay and solution.

Here this is the Government office so the holidays would be on all Sunday's, 2nd and 4th Saturday and some other fixed Holidays those are festivals. I have a table which contains all the holiday list including all Sunday, 2nd, 4th Saturday and Festival holidays. I tried below Query and I am getting one date but if that date is in holiday list then i need a date which is not in the holiday list.

select CONVERT(CHAR(10), dt+(select count(*) from Holiday_list where Date_Fmt between School_update and dt ),103) cnt from (
select case when Service_TypeID='1' then (School_update + 30) else (School_update + 5) end as dt,School_update from Application_Status)a

Thanks in Advance.
Tan Chee Yong 8-Oct-20 6:45am
   
If the non-working is in a table and the 30 and 5 are calendar days, you can try the below sql code

DECLARE @StartDate DATE
SET @StartDate = '20201007'

DECLARE @ServiceType INT;
SET @ServiceType = 1;

DECLARE @NoOfWorkingDays INT;
If @ServiceType = 1
	SET @NoOfWorkingDays = 30
ELSE 
	SET @NoOfWorkingDays = 5;

DECLARE @Counter INT; 
DECLARE @DayAdd INT; 
SET @DayAdd =1;

DECLARE @CurrDate datetime;
SET @CurrDate = DATEADD(DAY,@NoOfWorkingDays,@StartDate);

IF (@CurrDate IN (SELECT Date_FMT FROM TBL_Holidays))
BEGIN
	SET @CurrDate = DATEADD(DAY,1,@CurrDate);
	WHILE (@CurrDate IN (SELECT Date_FMT FROM TBL_Holidays))
	BEGIN
		SET @CurrDate = DATEADD(DAY,1,@CurrDate);
	END
END
SELECT @CurrDate;
KriShna RaJendra N PraSad 8-Oct-20 7:51am
   
Thank you so much Sir for your quick solution. one small help the start date is in another table the table name called as Application_Status and column name is School_update
Tan Chee Yong 8-Oct-20 22:34pm
   
you can use something like
SET @StartDate = (SELECT School_update FROM Application_Status);
KriShna RaJendra N PraSad 9-Oct-20 1:37am
   
Thank you so much...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900