Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Use Date calculation result in Case statement

Posted on 2012-03-27
14
Medium Priority
?
439 Views
Last Modified: 2012-03-27
Perplexed on this one...

I have an existing case statement that looks like this:
Case
when Priority = 0 then Actual_Hours <= 336 then 'True'
when Priority = 1 then Actual_Hours <= 72 then 'True'
when Priority = 2 then Actual_Hours <= 4 then 'True'
when Priority = 3 then Actual_Hours <= 1 then 'True'
else 'False'

I have since found out that the 'Actual_Hours' field wasn't valid so I need to do some date calculations to get it.

I am using another case statement to do this:
Case
when Status >= 6 then
(datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),dateadd[second],Actual_End_Date, 'Jan 1, 1970')))
else (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),getdate()))
end Hours

Now what I need to do is combine the two. I need to use the results of the Case Hours in the first Case. So instead of using the 'Actual_Hours' for the compare I need it be from the results of the Case Hours.

Make sense? BTW - I can't do a make table to stick them in.....
0
Comment
Question by:edrz01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 37771002
You can include entire select statement like a dynamic table

select CASE .... END from
(

   your select statement with case results
) tab
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37771020
Try this

Case
when Priority = 0 then (Case when Status >= 6 then (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),dateadd[second],Actual_End_Date, 'Jan 1, 1970'))) else (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),getdate())) end) Hours <= 336 then 'True'
when Priority = 1 then (Case when Status >= 6 then (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),dateadd[second],Actual_End_Date, 'Jan 1, 1970'))) else (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),getdate())) end) Hours <= 72 then 'True'
when Priority = 2 then (Case when Status >= 6 then (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),dateadd[second],Actual_End_Date, 'Jan 1, 1970'))) else (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),getdate())) end) Hours <= 4 then 'True'
when Priority = 3 then (Case when Status >= 6 then (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),dateadd[second],Actual_End_Date, 'Jan 1, 1970'))) else (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),getdate())) end) Hours <= 1 then 'True'
else 'False'

Open in new window

0
 

Author Comment

by:edrz01
ID: 37771055
rajvja, I could not follow your example.

sachinpatil10d, I am trying your example but I am getting an error "Incorrect syntax near 'Hours'.
Any idea?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 400 total points
ID: 37771182
Hi

What I am trying to say is
SELECT
when Priority = 0 then Actual_Hours <= 336 then 'True'
when Priority = 1 then Actual_Hours <= 72 then 'True'
when Priority = 2 then Actual_Hours <= 4 then 'True'
when Priority = 3 then Actual_Hours <= 1 then 'True'
END
(
SELECT Case
when Status >= 6 then
(datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),dateadd[second],Actual_End_Date, 'Jan 1, 1970')))
else (datediff(hh,dateadd[second],Create_Date, 'Jan 1, 1970'),getdate()))
end Hours, Priority
FROM <tablename>
) tab
0
 

Author Comment

by:edrz01
ID: 37771197
What does the 'tab' do? is it just a place holder?
0
 
LVL 11

Expert Comment

by:rajvja
ID: 37771206
Yes, it is a place holder.

I think you can remove tab if you dont need any where clause further.

If you have where clause, you must alias the inner sql statement
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 37771640
"tab" is an alias for the derived table and is absolutely required in SQL Server.

You can use a derived table to generate the result, then use an outer query to process the derived table.  That is a pain to code and maintain, and must be repeated everywhere you need to do this calc.

Instead, I suggest adding a computed column to the original base table:


ALTER TABLE dbo.original_table_name
ADD Hours AS
Case
when Status >= 6 then
(datediff(hh,dateadd[second],Create_Date, '19700101'),dateadd[second],Actual_End_Date, 'Jan 1, 1970')))
else (datediff(hh,dateadd[second],Create_Date, '19700101'),getdate()))
end


From then on, you can use "Hours" in any query, just as if it were any other column.


SELECT
    ...,
    Case
    when Priority = 0 and Hours <= 336 then 'True'
    when Priority = 1 and Hours <= 72 then 'True'
    when Priority = 2 and Hours <= 4 then 'True'
    when Priority = 3 and Hours <= 1 then 'True'
    else 'False' end AS HoursOK?,
    ...
FROM dbo.original_table_name
WHERE
   ... AND
    Hours .... --optional, just to show you can use the new virtual column in WHERE
ORDER BY
    Hours  --optional, just to show you can use the new virtual column in ORDER BY
0
 

Author Comment

by:edrz01
ID: 37771650
Scott, thanks but I can't alter the table as it will cause the application to freak if it sees the table different than expected.

Any other ideas?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37771837
Hmm, not sure I understand what you mean.

The application should be SELECTing the columns it needs -- another column shouldn't make any difference.


Next best would be to use a view with the "computed"/new column in the view:


CREATE VIEW dbo.<your_view_name_here>
AS
SELECT *,
    Case
    when Status >= 6 then
    (datediff(hh,dateadd[second],Create_Date, '19700101'),dateadd ([second],Actual_End_Date, 'Jan 1, 1970')))
    else (datediff(hh,dateadd[second],Create_Date, '19700101'),getdate()))
    end AS hours
FROM dbo.table_name


Then for any query that needs the "hours" column, use the view name instead of the original table name:


SELECT ..., hours, ...
FROM dbo.<your_view_name_here>
...
0
 

Author Comment

by:edrz01
ID: 37771862
Scott,

I appreciate your efforts on this but I can't make any changes to teh database for the reason stated above and because I don't own it. I can only do selects from it. To make any changes would be an act of congress and we know how effective (and fast) that is.

I will continue to try to find a work around....
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37771931
You can use a CTE to basically create an "in-line view".  Then put in the request to add a computed column to the original table, to replace the CTE with later :-) .



;WITH <your_name_here> AS
(
SELECT *,
    Case
    when Status >= 6 then
    (datediff(hh,dateadd[second],Create_Date, '19700101'),dateadd ([second],Actual_End_Date, 'Jan 1, 1970')))
    else (datediff(hh,dateadd[second],Create_Date, '19700101'),getdate()))
    end AS hours
FROM dbo.table_name
)
SELECT ..., CASE WHEN hours ... END, ...
FROM <your_name_here>
WHERE ...
0
 

Author Comment

by:edrz01
ID: 37771955
Scott, This is just a portion of the actual query...

Would I start the query with the ;WITH statement and then the rest?
0
 

Author Comment

by:edrz01
ID: 37772028
All, I got this working finally. I appreciate the work you did. While none of the solutions provided worked to sole this but I will have to give Scott the points as he stuck it out to the end.

My final solution simply used a

Select Case
when Priority = 'Low' then Actual_Hours <= 336 then 'True'
when Priority = 'Medium' then Actual_Hours <= 72 then 'True'
when Priority = 'High' then Actual_Hours <= 4 then 'True'
when Priority = 'Urgent' then Actual_Hours <= 1 then 'True'
Else 'False'
END 'Met Time To Resolve',*
from (
SELECT .....
) s
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37772187
I said it, I probably just didn't say it clearly enough, and in the right way:
"
You can use a derived table to generate the result, then use an outer query to process the derived table.  That is a pain to code and maintain, and must be repeated everywhere you need to do this calc.
"

from (...) [AS] alias

is a derived table, and the "SELECT ..." before that is an "outer query", in "sql speak" :-) .
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

609 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