Solved

Use Date calculation result in Case statement

Posted on 2012-03-27
14
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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:
Scott Pletcher 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
 

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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL to Update Table Dynamically 2 63
configure service broker on all databases 2 94
Query to Add Late Tolerance 10 82
SQL Server Configuration Manager WMI Error 11 41
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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