CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
mdevm
mdevm

Reputation: 97

How to return the correct start date value from a stored procedure in this case?

I am using below case condition to calculate a StartDate variable:

DECLARE @StartDate Date =
   DATEADD(DAY, CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN -10 ELSE -8 END, GETDATE())

AS you can see, if today's date is Monday then 10 days are subtracted from today's date, else 8 days are deducted. Since today (10/16/2018) is not a Monday, this would return 10/8/2018 as an output (8 days deduction). If executed on Monday (10/15) then @StartDate would return 10/5 as output.

I added a table called Holidays and it has two columns Holiday Name and Date containing three records for this year: Columbus Day: 10/8, Veteran day 11/12 and Thanksgiving 11/22.

Now the challenging part I have to do is, if the returned output of @StartDate is 10/8 (query executed today: 10/16) which is a holiday (Monday), then value of @StartDate should be changed to 10/5 (previous business day, Saturday & Sunday excluded). Also if the value of @StartDate is 10/5 (query executed on 10/15), then the value should be changed to 10/4 since 10/8 was a holiday, it won't be counted for deduction of 10 days so instead of 10/5, it should be 10/4.

So in theory it should work as: check if there are any holidays that fall between today's date and @StartDate, and if so then use prior day and adjust for Mondays accordingly based on the scenario I mentioned above.

Please note that the included statement I mentioned at the top is part of a stored procedure.

How can I make this work, can someone help? Thanks in advance.

Upvotes: 1

Views: 272

Answers (1)

TomC
TomC

Reputation: 2804

Assuming that there are never multiple dates in a row in your holiday, this will do it in a single(ish) statement. You could easily break this into two if you prefer:

DECLARE @StartDate date

;with d as (
select convert(date,dateadd(day,case when datepart(dw,getdate())=2 then -10 else -8 end,getdate())) dt
)
select @StartDate=case when holiday.date is null then dt else dateadd(day,-1,dt) end
from d 
left join holiday on holiday.date=dt

EDIT: This is a complete working copy. I create a table variable to hold holidays, run the initial query, then insert the returned date as a holiday, then run the query again. If you run this in sql server you will see that the second query returns the day before the first query.

declare @holiday table(date date)
DECLARE @StartDate date

;with d as (
select convert(date,dateadd(day,case when datepart(dw,getdate())=2 then -10 else -8 end,getdate())) dt
)
select @StartDate=case when holiday.date is null then dt else dateadd(day,-1,dt) end
from d 
left join @holiday holiday on holiday.date=dt

select @startdate

insert @holiday values (@startdate)

;with d as (
select convert(date,dateadd(day,case when datepart(dw,getdate())=2 then -10 else -8 end,getdate())) dt
)
select @StartDate=case when holiday.date is null then dt else dateadd(day,-1,dt) end
from d 
left join @holiday holiday on holiday.date=dt

select @startdate

Upvotes: 1

Related Questions