?
Solved

complex case statement

Posted on 2003-03-12
18
Medium Priority
?
336 Views
Last Modified: 2012-06-21
Hello Experts !!

I have this problem with my SQL stored procedure. Basically the stored procedure below works just fine and it uses a case statement to test for several different conditions. All of the tests are from one single table (the session table). However I want to add in another test within the case statement. This new test should grab a value from another table called 'Location'. I want to know if this is possible ?

If you need any further explanation please dont hessitate to say and ill to to elaborate further. :)


Regards
Rob

Ps: as you can imagine the case statement below gets repeated 40 times but for simplicity i've only included it the once.

====================================================
Select Computers.Computer_name,

Max ( Case
--STATEMENT TO GET VALUE FROM ANOTHER TABLE HERE !! BUT IN THE SAME CASE STATEMENT!!
When ( Computers.Out_Of_Order  = 1 ) Then 2
When ( Session.User_id = "CLASS" ) And ( Session.Session_start_time <= ( DateAdd ( Minute , @Room_Increment , @Current_Time ) ) And Session.Session_end_time > ( DateAdd ( Minute , @Room_Increment , @Current_Time ) ) ) Then 3
When ( Session.Session_start_time <= ( DateAdd ( Minute , @Room_Increment , @Current_Time ) ) And Session.Session_end_time > ( DateAdd ( Minute , @Room_Increment , @Current_Time ) ) ) Then 1
Else 0 End ) As 'T2',

From Computers
Left Join Session
On Computers.Computer_name=Session.Computer_name
where Computers.Location_id = @Location_id
Group By Computers.Computer_name
GO
0
Comment
Question by:Rob_2002
[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
  • 9
  • 3
  • 3
  • +2
18 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 8123442
You cannot use a query or sub-query in a CASE statement.  You need to:

to {inner/left/right} join the table in the normal way and reference the other table columns as with Session table
OR
convert the query above to a derived table, adding the columns from another table as sub-queries, then write an outer query on the derived table that does the CASE
0
 

Author Comment

by:Rob_2002
ID: 8123489
Hi Scott,

Hmm I see where your comming from. This would make my query really slow down somewhat I would imagine. But I guess there is nothing I can do about that.

Would you be so kind as to tell me how I would go about putting this into a session table and then reading from that session table afterwards ?

Also would I then 'drop' the session table ?

Regards
Rob
0
 

Author Comment

by:Rob_2002
ID: 8123495
Hi Scott,

Hmm I see where your comming from. This would make my query really slow down somewhat I would imagine. But I guess there is nothing I can do about that.

Would you be so kind as to tell me how I would go about putting this into a session table and then reading from that session table afterwards ?

Also would I then 'drop' the session table ?

Regards
Rob
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8123524
If you could, please provide a general shell of the query to retrieve the new value.  I have no idea which table and column it should reference or how it might relate to columns and tables of the current query.

Note that it will be some time before I get back to this because it is going home time here.
0
 
LVL 7

Assisted Solution

by:TroyK
TroyK earned 300 total points
ID: 8123535
Rob;

The searched case expression will "short-circuit" on the first true condition. If you are looking for the maximum value of your conditions in the CASE expression above (my assumption because you tried to surround it with MAX(...)), simply order your conditions by importance. The CASE expression will then evaluate to the number corresponding to the first true expression. A simple example:

SELECT CASE WHEN 'a' = 'a' THEN 3
          WHEN 'b' = 'b' THEN 2
          WHEN 'c' = 'c' THEN 1
          ELSE 0 END

HTH,
TroyK, MCSD
0
 

Author Comment

by:Rob_2002
ID: 8128093
Hi Scott,

thanks for your reply. at the moment the query is only reading values from the 'session' table. these are, session_start_time and session_end_time. the query then does a left join against the computers table. which works great (thats shown above). The problem i have is that the case statement also needs to be able the read another value ("close_time") from the location table, and then simply test to see if the @current time is greater or equal to the location.close time.

however as i am using a left join already to my computers table i will need to use a session table as you have suggested.

does this make sense ?

rob
0
 

Author Comment

by:Rob_2002
ID: 8128152
sessions

user_id            as string
computer_name      as string
session_start_time as datetime
session_end_time   as datetime

computers

computer_name      as string
location_id        as integer

location

location_id as integer

open_time as datetime
close_time as datetime
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8129426
If you need this today, I hope someone else looks at this and can help.  I'm swamped for the rest of the day today and so won't get back to it until tomorrow.
0
 
LVL 7

Expert Comment

by:TroyK
ID: 8130188
Rob;

Is this what you're after? I joined the Location table in the FROM clause, then added the check (which I coded to 99 for this sample).

------ SQL -----
Select Computers.Computer_name
 ,
MAX(
Case
WHEN ( @Current_Time >= Location.close_time ) THEN 99
When ( Computers.Out_Of_Order  = 1 ) Then 2
When ( Session.User_id = 'CLASS' )
  And ( Session.Session_start_time <= ( DateAdd ( Minute , @Room_Increment , @Current_Time ) )
  And Session.Session_end_time > ( DateAdd ( Minute , @Room_Increment , @Current_Time ) ) ) Then 3
When ( Session.Session_start_time <= ( DateAdd ( Minute , @Room_Increment , @Current_Time ) )
  And Session.Session_end_time > ( DateAdd ( Minute , @Room_Increment , @Current_Time ) ) ) Then 1
Else 0
End)  'T2'

From Computers
INNER JOIN Location
ON Computers.Location_ID = Location.Location_ID
Left Join Session
On Computers.Computer_name=Session.Computer_name
where Computers.Location_id = @Location_id
Group By Computers.Computer_name
GO
------ END SQL -----

HTH,
TroyK, MCSD
0
 

Author Comment

by:Rob_2002
ID: 8138325
Hi TroyK,

Sorry I've not gotten back to you sooner, work problems Im afrad.

I have tryed to use the query you game me however it seems to return 99 every time i run it, no matter what time i pass in to it.

for example if the location.close_time is 17:00:00 and i pass in 16:00:00 i would expect it to carry on and goto the next case statement but it does not.

do you have any ideas as to why ?

Regards

Rob
0
 

Author Comment

by:Rob_2002
ID: 8138423
Hi TroyK,

Sorry I've not gotten back to you sooner, work problems Im afrad.

I have tryed to use the query you game me however it seems to return 99 every time i run it, no matter what time i pass in to it.

for example if the location.close_time is 17:00:00 and i pass in 16:00:00 i would expect it to carry on and goto the next case statement but it does not.

do you have any ideas as to why ?

Regards

Rob
0
 

Author Comment

by:Rob_2002
ID: 8138438
Hi TroyK,

Sorry I've not gotten back to you sooner, work problems Im afrad.

I have tryed to use the query you game me however it seems to return 99 every time i run it, no matter what time i pass in to it.

for example if the location.close_time is 17:00:00 and i pass in 16:00:00 i would expect it to carry on and goto the next case statement but it does not.

do you have any ideas as to why ?

Regards

Rob
0
 

Author Comment

by:Rob_2002
ID: 8138762
Hi TroyK,

Sorry I've not gotten back to you sooner, work problems Im afrad.

I have tryed to use the query you game me however it seems to return 99 every time i run it, no matter what time i pass in to it.

for example if the location.close_time is 17:00:00 and i pass in 16:00:00 i would expect it to carry on and goto the next case statement but it does not.

do you have any ideas as to why ?

Regards

Rob
0
 
LVL 7

Expert Comment

by:TroyK
ID: 8139607
Rob;

Have you checked the date portion of the time you're comparing? Since it looks like you're only interested in the hours portion, you may need to scrub the date off.

TroyK
0
 

Author Comment

by:Rob_2002
ID: 8142845
Hi TroyK,

Thanks for that I will look into it further this weekend and get back to you. I've not forgotten you :)

Thanks

Rob
0
 

Expert Comment

by:CleanupPing
ID: 9275942
Rob_2002:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11093079
No comment has been added to this question in more than 257 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: ScottPletcher http:#8123442, TroyK http:#8123535

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

801 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