Use Date calculation result in Case statement

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.....
edrz01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajvjaCommented:
You can include entire select statement like a dynamic table

select CASE .... END from
(

   your select statement with case results
) tab
sachinpatil10dCommented:
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

edrz01Author Commented:
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?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

rajvjaCommented:
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
edrz01Author Commented:
What does the 'tab' do? is it just a place holder?
rajvjaCommented:
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
Scott PletcherSenior DBACommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edrz01Author Commented:
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?
Scott PletcherSenior DBACommented:
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>
...
edrz01Author Commented:
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....
Scott PletcherSenior DBACommented:
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 ...
edrz01Author Commented:
Scott, This is just a portion of the actual query...

Would I start the query with the ;WITH statement and then the rest?
edrz01Author Commented:
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
Scott PletcherSenior DBACommented:
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" :-) .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.