I have a CASE statement that checks a "Rush Delivery" Table.

RUSH DELIVERY Table has the following columns:
RushNo, RushType
1001 '10 Day Expedite'
1005 '5 Day Expedite'
1010 'Immediate Expedite'
......AND SO ON.....

I need to check the RUSH TYPE as I have in the code below---AND THEN I need to check if the J.JobDate + 10 falls on a weekend (Saturday or Sunday).

If it falls on a Saturday, I will need to J.JobDate + 12.
If it falls on a Sunday, I will need to J.JobDate + 11.

Same for the rest of the RUSH TYPES.

CASE RM.RushNo WHEN 1001 THEN J.JobDate + 10 WHEN 1005 THEN J.JobDate + 5 WHEN 1010 THEN J.JobDate WHEN 1012 THEN J.JobDate + 4 WHEN 1013 THEN J.JobDate + 3 WHEN 1014 THEN J.JobDate + 2 WHEN 1016 THEN J.JobDate + 7 WHEN 1017 THEN J.JobDate + 8 WHEN 1018 THEN J.JobDate + 9 WHEN 1019 THEN J.JobDate + 1 WHEN 1020 THEN J.JobDate + 6END AS DueDate,

I do not think your date add will work the way you have it, you should use a function like this
DATEADD (dd, 10, J.JobDate)

do this as a sub case, but if you are doing this in a SP there would be a cleaner way to do this.

WHEN 1001 THEN
-- nested case here
-- this takes the date and adds 10 then gets the part of the date and returns a number 1=Sunday 7=Saturday
Case datepart(weekday, DATEADD (dd, 10, J.JobDate))
When 7 Then DATEADD (dd, 12, J.JobDate)
When 1 Then DATEADD (dd, 11, J.JobDate)
End
-- end nested case

Or instead of nested case you could create a User Defined Function that you would call that would be much cleaner. So in your above case statement you would do
WHEN 1001 Then CallUserDefinedFunctionHere(J.JobDate, 10)

J.JobDate +
CASE RM.RushNo
WHEN 1001 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 10)
WHEN 'Saturday' THEN 12
WHEN 'Sunday' THEN 11
ELSE 10
END
WHEN 1005 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 5)
WHEN 'Saturday' THEN 7
WHEN 'Sunday' THEN 6
ELSE 5
END
WHEN 1010 THEN
CASE DATENAME(WEEKDAY, J.JobDate)
WHEN 'Saturday' THEN 2
WHEN 'Sunday' THEN 1
ELSE 0
END
WHEN 1012 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 4)
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 5
ELSE 4
END
WHEN 1013 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 3)
WHEN 'Saturday' THEN 5
WHEN 'Sunday' THEN 4
ELSE 3
END
WHEN 1014 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 2)
WHEN 'Saturday' THEN 4
WHEN 'Sunday' THEN 3
ELSE 2
END
WHEN 1016 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 7)
WHEN 'Saturday' THEN 9
WHEN 'Sunday' THEN 8
ELSE 7
END
WHEN 1017 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 8)
WHEN 'Saturday' THEN 10
WHEN 'Sunday' THEN 9
ELSE 8
END
WHEN 1018 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 9)
WHEN 'Saturday' THEN 11
WHEN 'Sunday' THEN 10
ELSE 9
END
WHEN 1019 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 1)
WHEN 'Saturday' THEN 3
WHEN 'Sunday' THEN 2
ELSE 1
END
WHEN 1020 THEN
CASE DATENAME(WEEKDAY, J.JobDate + 6)
WHEN 'Saturday' THEN 8
WHEN 'Sunday' THEN 7
ELSE 6
END
END AS DueDate,

0

At Springboard, we know how to get you a job in data science. With Springboardâ€™s Data Science Career Track, youâ€™ll master data science with a curriculum built by industry experts. Youâ€™ll work on real projects, and get 1-on-1 mentorship from a data scientist.

J.JobDate + RUSH TYPE = Next WeekDay Date... (Since today is 06/13/08, I would need to list all jobs for 06/16/2008)

So for example, consider the following data:
JobDate RushType DueDate
06/06/2008 10 Day Expedite 06/16/2008
06/10/2008 4 Day Expedite 06/16/2008 -- include the weekends
06/13/2008 1 Day Expedite 06/16/2008 -- Next Business day would be Monday 06/16/2008

Solved! I will go with repeating the CASE statement in the WHERE clause.

I now have another question! I want to award you points for this Anser, and start a new thread related to this question.

However, I do not want to ask the new question in the main Lounge since me and you are already on the same page, and the question is related to this query.

So is there a way I can award you points for this Answer, and have you take a look at a new thread that I am about to create somehow?

Its all the same to me in my other question...If its easier for you to alter your own CASE statement, then be my guest...How do I alter your CASE statement below to not count Weekends when adding...

J.JobDate + CASE RM.RushNo WHEN 1001 THEN CASE DATENAME(WEEKDAY, J.JobDate + 10) WHEN 'Saturday' THEN 12 WHEN 'Sunday' THEN 11 ELSE 10 END WHEN 1005 THEN CASE DATENAME(WEEKDAY, J.JobDate + 5) WHEN 'Saturday' THEN 7 WHEN 'Sunday' THEN 6 ELSE 5 END WHEN 1010 THEN CASE DATENAME(WEEKDAY, J.JobDate) WHEN 'Saturday' THEN 2 WHEN 'Sunday' THEN 1 ELSE 0 END WHEN 1012 THEN CASE DATENAME(WEEKDAY, J.JobDate + 4) WHEN 'Saturday' THEN 6 WHEN 'Sunday' THEN 5 ELSE 4 END WHEN 1013 THEN CASE DATENAME(WEEKDAY, J.JobDate + 3) WHEN 'Saturday' THEN 5 WHEN 'Sunday' THEN 4 ELSE 3 END WHEN 1014 THEN CASE DATENAME(WEEKDAY, J.JobDate + 2) WHEN 'Saturday' THEN 4 WHEN 'Sunday' THEN 3 ELSE 2 END WHEN 1016 THEN CASE DATENAME(WEEKDAY, J.JobDate + 7) WHEN 'Saturday' THEN 9 WHEN 'Sunday' THEN 8 ELSE 7 END WHEN 1017 THEN CASE DATENAME(WEEKDAY, J.JobDate + 8) WHEN 'Saturday' THEN 10 WHEN 'Sunday' THEN 9 ELSE 8 END WHEN 1018 THEN CASE DATENAME(WEEKDAY, J.JobDate + 9) WHEN 'Saturday' THEN 11 WHEN 'Sunday' THEN 10 ELSE 9 END WHEN 1019 THEN CASE DATENAME(WEEKDAY, J.JobDate + 1) WHEN 'Saturday' THEN 3 WHEN 'Sunday' THEN 2 ELSE 1 END WHEN 1020 THEN CASE DATENAME(WEEKDAY, J.JobDate + 6) WHEN 'Saturday' THEN 8 WHEN 'Sunday' THEN 7 ELSE 6 END END AS DueDate,

this not a solution, just a few comments to consider.

If the db is locked down, then you should not be able to write a select statement

A conditional statement in a Where clause forces SQL to ignore statistics
Never use a case statement in the where clause unless Performance is unessary and Querys that take days are acceptable. (= > < between and in (Indexed Column)) are ok

brad2575 actually was on the best track right from the start.
But I would go further and create the function
dbo.ufn_RushDate(J.JobDate,RM.RushNo) Replacing the Case statment completely.

If you can't create the function your self becuase the database is locked down,
maybe you can suggest to the database owners the creation of your function.

If you need to filter your results based on the DueDate.
Then you should create a temp table of PrimaryKeys and DueDates.
Then Join on the Filtered Temp Table

I would also suggest adding a lookup table for hoildays since this was a requirement for a similar issue
Since if the Delivery date was Monday Dec 25 you might want to add another day

0

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

SELECT Column1, Column2, Column3, JobDate, DueDate

FROM (

SELECT Column1, Column2, Column3, J.JobDate,

CASE RM.RushNo

.........................

........................

........................

END AS DueDate

FROM Table1 Join Table 2 ON....................

..........................

) AS derived

WHERE DueDate = Getdate()+1

OR, repeat the entire DueDate expression in the WHERE:

SELECT Column1, Column2, Column3, J.JobDate,

CASE RM.RushNo

.........................

........................

........................

END AS DueDate

FROM Table1 Join Table 2 ON....................

..........................

) AS derived

WHERE CASE RM.RushNo

.........................

........................

........................

END = Getdate()+1