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

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.

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

select CASE .... END from
(

your select statement with case results
) tab
Commented:
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'
``````
Author Commented:

sachinpatil10d, I am trying your example but I am getting an error "Incorrect syntax near 'Hours'.
Any idea?
Commented:
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
end Hours, Priority
FROM <tablename>
) tab
Author Commented:
What does the 'tab' do? is it just a place holder?
Commented:
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
Senior 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
Case
when Status >= 6 then
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

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

Author 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?
Senior 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
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>
...
Author 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....
Senior 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
end AS hours
FROM dbo.table_name
)
SELECT ..., CASE WHEN hours ... END, ...
FROM <your_name_here>
WHERE ...
Author Commented:
Scott, This is just a portion of the actual query...

Would I start the query with the ;WITH statement and then the rest?
Author 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
Senior 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.