Dear friends I have a task to show the date of working day using SQL.

Question

1.00/5 (1 vote)

See more:

, +

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 7-Oct-20 21:07pm

KriShna RaJendra N PraSad

Updated 7-Oct-20 21:50pm

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tan Chee Yong · Accepted Answer · 2020-10-07T21:51:00

Solution 1

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;

Posted 7-Oct-20 21:51pm

Tan Chee Yong

Updated 8-Oct-20 0:01am

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...