SQL CASE Statement Query

Hello Experts, I require your expert knowledge.

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 + 6
END AS DueDate,

Open in new window

LVL 8
pzozulkaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:

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
0
 
brad2575Commented:
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)

This way it is much cleaner and easier to see.
0
 
Scott PletcherSenior DBACommented:
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
A proven path to a career in data science

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.

 
Scott PletcherSenior DBACommented:
Instead of hard-coding the days, you should add them to the RushDelivery table, like so:

RushNo, Days, RushType
1001   10      '10 Day Expedite'
1005     5      '5 Day Expedite'
1010     0      'Immediate Expedite'
...

Then you can add the "days" column instead of a literal # days, which means that for modifications, only the table has to be changed, not code.
0
 
pzozulkaAuthor Commented:
The DB is locked. We cannot make alterations to the DB.

My question is, after creating this CASE statement we say "END AS 'DueDate'.

This is a "Virtual Column" that does not really exist in any table. I need to later in the WHERE clause specify:

WHERE DUEDATE  = GETDATE() + 1

But it returns an error saying 'obviously' that there is no column name DUEDATE.
0
 
Scott PletcherSenior DBACommented:
My code was intended to just replace the snippet of code you replaced.

My code should return an "AS DueDate" just like yours did, except that it will include the extra day(s) for weekend adjustment.
0
 
pzozulkaAuthor Commented:
ULTIMATELY, I need to have the following:

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
0
 
pzozulkaAuthor Commented:
Right it ends as Column -- DueDate. I need to use this column in the WHERE statement to specify to list only jobs that are DUE "Next Business Day".
0
 
Scott PletcherSenior DBACommented:
"Next WeekDay Date" is what mine should return, based on JobDate and RushTypeCode.
0
 
Scott PletcherSenior DBACommented:
Oh, oh, OK, sorry, I'm with you now.

Easiest is to add an outer query, making the original query a subquery, like this:

SELECT *
FROM (
    SELECT 1 + 3 + 5 AS newCol --<<-- computed col
) AS derived
WHERE newCol >= 5


0
 
pzozulkaAuthor Commented:
ScottPletcher, I think we are on the same page, but I'm not sure I follow what you are trying to say.  Please elaborate.

My Structure looks like this for now:

SELECT Column1, Column2, Column3,  J.JobDate,
CASE RM.RushNo
.........................
........................
........................
END AS DueDate

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

WHERE DueDate = Getdate()+1
0
 
pzozulkaAuthor Commented:
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?



0
 
Scott PletcherSenior DBACommented:
Yes.  You can accept (or hold off on accepting a bit) an answer to this q,

but post a link to the next q back in this q (just copy the new q's url from the top of the screen into a new comment on this q).

That's a common thing, people do it here all the time.
0
 
pzozulkaAuthor Commented:
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,

Open in new window

0
 
BanthorCommented:
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.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.