Solved

SQL CASE Statement Query

Posted on 2008-06-13
16
782 Views
Last Modified: 2008-06-23
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

0
Comment
Question by:pzozulka
16 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 21781878
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 21782092
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 21782123
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
 
LVL 8

Author Comment

by:pzozulka
ID: 21782545
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 21782577
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
 
LVL 8

Author Comment

by:pzozulka
ID: 21782618
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
 
LVL 8

Author Comment

by:pzozulka
ID: 21782624
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 21782631
"Next WeekDay Date" is what mine should return, based on JobDate and RushTypeCode.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 21782653
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
 
LVL 8

Author Comment

by:pzozulka
ID: 21782741
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 21782807

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
 
LVL 8

Author Comment

by:pzozulka
ID: 21783006
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 21783111
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
 
LVL 8

Author Comment

by:pzozulka
ID: 21783151
0
 
LVL 8

Author Comment

by:pzozulka
ID: 21783169
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
 
LVL 10

Expert Comment

by:Banthor
ID: 21783638
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now