Solved

# Use Date calculation result in Case statement

Posted on 2012-03-27
396 Views
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.....
0
Question by:edrz01
• 6
• 4
• 3
• +1

LVL 11

Expert Comment

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

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'
``````
0

Author Comment

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

LVL 11

Assisted Solution

rajvja earned 100 total points
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
0

Author Comment

What does the 'tab' do? is it just a place holder?
0

LVL 11

Expert Comment

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

ScottPletcher earned 400 total points
"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
0

Author Comment

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

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>
...
0

Author Comment

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

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 ...
0

Author Comment

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

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

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

### Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …