CODEWITHSUNDEEP
sqlt-sqlreporting-services
Audioillity
Audioillity

Reputation: 313

SQL - WHERE clause Return results with next working day

I seem to be asking a lot of SQL questions at the moment. (I would normally write a program to sort the data into a report table, however at the moment that is not possible, and needs to be done using SQL)

The Question I need a where clause that returns results with the next working day. i.e. Monday 01/01/01 the next working day would be Tuesday 02/01/01 which could be achieved with a simple date add. However on a Friday 05/01/01 the next working day is Monday 08/01/01. Is there anything built in to help cope with this easily?

Thanks for your advice.

Upvotes: 1

Views: 7094

Answers (9)

storepeinture
storepeinture

Reputation: 1

It's been basically answered above, but it took me a while to get it and I thought maybe this will help somebody. Is used a simple CASE-WHEN.

This is a check in order to find out which days are weekdays

DATEPART(dw, date) -> MON through FRI = 1,2,3,4,5    SAT = 6,     SUN = 7

This is the CASE-WHEN:

CASE
    WHEN DATEPART(weekday, date) <= 5 THEN date       -- weekdays, no change
    WHEN DATEPART(weekday, date)  = 6 THEN date + 2   -- SAT + 2 = MON
    WHEN DATEPART(weekday, date)  = 6 THEN date + 1   -- SUN + 1 = MON
END AS 'WEEKDAY_DATES'

Check, if the statement actually gets you weekdays only:

CASE
    WHEN DATEPART(weekday, date) <= 5 THEN DATENAME(weekday, date)
    WHEN DATEPART(weekday, date)  = 6 THEN DATENAME(weekday, date + 2)
    WHEN DATEPART(weekday, date)  = 6 THEN DATENAME(weekday, date + 1)
END AS 'WEEKDAY_NAMES'

Upvotes: 0

FMFF
FMFF

Reputation: 1718

Here's what I did for a one-off application: 

        WHILE EXISTS (SELECT * FROM Holidays WHERE CONVERT(VARCHAR, HolidayDate,101) = CONVERT(VARCHAR,@DateVariable 101 ) OR DATENAME(WEEKDAY, @DateVariable)='SATURDAY' OR DATENAME(WEEKDAY, @DateVariable)='SUNDAY')
        BEGIN
            SET @DateVariable = DateAdd(day,1,@DateVariable)
            PRINT @DateVariable -- If you want
        END

Note that, our Holidays table stores all holidays for the past and future years.

Upvotes: 1

Danimal111
Danimal111

Reputation: 2062

All Credit to Cashif -- I modified his to include a Holiday table (tblHolidays with a date field HolDate) which is very lightweight -- about 10 rows a year if you're lucky enough to get that many days off!

My version returns date type (which I find easier to work with). I also run thru this more than once -- if Thursday is your startday and Friday is a holiday, you have to again add 2 more days to get to Monday (or the next working day). Then it checkes to make sure the next week doesn't start with a holiday.

CREATE FUNCTION [dbo].[GetNextWorkingDay] (@givenDate DATE)
RETURNS DATE
AS
BEGIN
    DECLARE @workingDate DATETIME
    IF (DATENAME(dw , @givenDate) = 'Friday')
    BEGIN
         SET @workingDate = DATEADD(day, 3, @givenDate)
    END
    ELSE IF (DATENAME(dw , @givenDate) = 'Saturday')
    BEGIN
         SET @workingDate = DATEADD(day, 2, @givenDate)
    END
    ELSE 
    BEGIN
         SET @workingDate = DATEADD(day, 1, @givenDate)
    END

    while ((Select  count(*) from tblHolidays where holdate = @workingDate) > 0)
    begin
        set @workingDate = dateadd(dd,1,@WorkingDate)
    end
    -- if adding a day makes it a Saturday, add 2 more to get to Monday (and test to make sure the week doesn't start with a holiday)
    IF (DATENAME(dw , @workingDate) = 'Saturday')
    BEGIN
         SET @workingDate = DATEADD(day, 2, @workingDate)
    END


    while ((Select  count(*) from tblHolidays where holdate = @workingDate) > 0)
    begin
        set @workingDate = dateadd(dd,1,@WorkingDate)
    end
    RETURN @workingDate
END

... of course you should refactor so the code doesn't repeat, and include a while clause to repeat only as many times as needed to get to a working day, but I have a deadline... that'll be for another less hectic day.

Upvotes: 1

CruelIO
CruelIO

Reputation: 18624

How about somethin like this?

select * from table
where (date = dateadd(dd,1,@today) and datepart(weekday,@today) not in (6,0) ) --its not friday or saturday 
or    (date = dateadd(dd,2,@today) and datepart(weekday,@today) = 0) -- its saturday 
or    (date = dateadd(dd,3,@today) and datepart(weekday,@today) = 6) --its friday

the date attribute should have the same time as @today or else you have to also use between

Upvotes: 0

no_one
no_one

Reputation: 1850

What you need is a calendar table. Getting the next working days is not so simple if you need to account for holidays other than the weekend. The table basically contains just two columns Date and an integer field indicating whether it is a working/non working day- but there can be other columns for - example quarter etc as well.

This table is populated once a year and then maintained as necessary. Getting the result for next working day then becomes as simple as a query like this.

SELECT field1,filed2 from your table T where your date_Field = (SELECT min(date) From calendar table where WorkingDay = 1 and date > GetDate())

/P

Upvotes: 0

Kashif
Kashif

Reputation: 14430

CREATE FUNCTION dbo.uf_GetNextWorkingDay (@givenDate DATETIME)
RETURNS DATETIME
AS
BEGIN

    DECLARE @workingDate DATETIME

    IF (DATENAME(dw , @givenDate) = 'Friday')
        BEGIN
             SET @workingDate = DATEADD(day, 3, @givenDate)
        END
    ELSE IF (DATENAME(dw , @givenDate) = 'Saturday')
        BEGIN
             SET @workingDate = DATEADD(day, 2, @givenDate)
        END
    ELSE 
        BEGIN
             SET @workingDate = DATEADD(day, 1, @givenDate)
        END

    RETURN @workingDate
END

A good article http://ryanfarley.com/blog/archive/2005/02/14/1685.aspx

Upvotes: 3

Josh
Josh

Reputation: 1001

You could do this with a simple case statement. 

select case when datepart(dw, getdate()) >= 6 then getdate() + (9 - datepart(dw, getdate())) else getdate() + 1 end

Upvotes: 0

Robin Day
Robin Day

Reputation: 102478

You could do this with a simple day check and add 3 days rather than one if its a Friday. However, do you need to take into consideration public holidays though?

Upvotes: 2

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340211

The key is to use the DATEPART(weekday,@date) function, it'll return the day of the week, so if it's saturday or sunday you just add one or two to the current date to get the desired result.

You can create a user defined function to do so easily, for example Pinal Dave has this 

CREATE FUNCTION dbo.udf_GetPrevNextWorkDay (@dtDate DATETIME, @strPrevNext VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
IF @strPrevNext = 'Previous'
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
ELSE
SET @rtResult = DATEADD(d,-1,@dtDate)
--To find Next working day
ELSE
IF @strPrevNext = 'Next'
IF @intDay = 6
SET @rtResult = DATEADD(d,3,@dtDate)
ELSE
IF @intDay = 7
SET @rtResult = DATEADD(d,2,@dtDate)
ELSE
SET @rtResult = DATEADD(d,1,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO

Upvotes: 4

Related Questions