December 13, 2011 at 4:07 pm
Hi,
I want to get a deliverydate as receivedate+4 or receivedate+6 days and which should not fall on holidays and weekends.
example:
SELECT * , case when column1 = column2
then dateadd(d,4,receivedate)
Else dateadd(d,6,Receiveddate) end As DeliveryDate
from TableA
I could get the delivarydate date but it falls on holidays and weekends so can any one help me how to eliminate holidays and weekends from in the above code.
Thanks.
December 13, 2011 at 11:40 pm
Do you have a date table somewhere that lists all the dates together with an holiday flag or something like that?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 11:59 pm
I find a calendar table invaluable ...
December 14, 2011 at 12:20 am
Dave Ballantyne (12/13/2011)
I find a calendar table invaluable ...
Indeed. If the OP doesn't have a calendar table that indicates holidays, than the request can't be solved, as holidays vary from country to country, and the dates can change from year to year.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2011 at 3:33 am
Hi,
Below procedure will provide estimated date to avoid the week ends. Hoildays will change from company to company.
CREATE PROC dbo.Usp_deliverydate @Provideddate DATETIME = NULL,
@Returndate DATETIME OUTPUT
AS
BEGIN
--Set default first day as sunday
SET datefirst 7
DECLARE @day VARCHAR(20)
IF @Provideddate IS NULL
BEGIN
SET @Provideddate = CONVERT(VARCHAR(10), Getdate(), 101)
END
SET @Returndate = Dateadd(dd, 4, @Provideddate)
SELECT @day = CASE Datepart(dw, CONVERT(VARCHAR(10), @Returndate, 101))
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
-- PRINT @day
-- Adding 5 days for the date provided if it is sunday add one more date
IF @day = 'SUNDAY'
BEGIN
SET @Returndate = Dateadd(dd, 1, @Returndate)
END
IF @day = 'SATURDAY'
BEGIN
SET @Returndate = Dateadd(dd, 2, @Returndate)
END
RETURN
END
-- Calling type
DECLARE @Redate datetime
EXEC dbo.usp_deliverydate '2011-12-13',@Returndate = @Redate output
PRINT @redate
Hope this helps some what. thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply