Solved

Use Date calculation result in Case statement

Posted on 2012-03-27
14
401 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
  • 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
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 100 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 69

Accepted Solution

by:
ScottPletcher earned 400 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 69

Expert Comment

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

Expert Comment

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

Expert Comment

by:ScottPletcher
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

948 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

19 Experts available now in Live!

Get 1:1 Help Now