Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

complex case statement

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
Rob_2002
Asked:
Rob_2002
  • 9
  • 3
  • 3
  • +2
2 Solutions
 
Scott PletcherSenior DBACommented:
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
 
Rob_2002Author Commented:
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
 
Rob_2002Author Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
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
 
TroyKCommented:
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
 
Rob_2002Author Commented:
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
 
Rob_2002Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
TroyKCommented:
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
 
Rob_2002Author Commented:
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
 
Rob_2002Author Commented:
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
 
Rob_2002Author Commented:
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
 
Rob_2002Author Commented:
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
 
TroyKCommented:
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
 
Rob_2002Author Commented:
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
 
CleanupPingCommented:
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
 
monosodiumgCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now