Solved

Use Date calculation result in Case statement

Posted on 2012-03-27
14
396 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What does the 'tab' do? is it just a place holder?
0
 
LVL 11

Expert Comment

by:rajvja
Comment Utility
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
Comment Utility
"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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:edrz01
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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: …

772 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

10 Experts available now in Live!

Get 1:1 Help Now