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
Solved

Select records based on boolean parameter

Posted on 2013-02-04
8
254 Views
Last Modified: 2013-02-04
I have a query and I'm trying to add code so that if the parameter @Client is true then only select records with a Class of either 2 or 6.  Else If @Client is false then select All classes.  Would you show me what I'm doing wrong?


select	workdate, empId, L.LastName + ', ' + L.FirstName AS Name, ManagerUID, 
convert(varchar(5), dateadd(second, Total_WorkDate_Units, '0:00:00'),108) as HrsMin,
		CASE WHEN @Clients = -1 then L.Class IN (2, 6) END  
from	Employee_Work_Units_Summary s inner join dbo.EmployeeList L on s.empId = L.EmployeeId
where	Total_WorkDate_Units >  32400 AND (L.Suspend = 0)  and   (ManagerUID = ISNULL(NULLIF (@Supervisor, 0), ManagerUID))  AND (@Employee =0 or L.EmployeeId =@Employee) 
Order by workdate, Name]

Open in new window

0
Comment
Question by:BobRosas
  • 4
  • 3
8 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38852591
CASE WHEN @Clients = -1 then L.Class IN (2, 6) END  

should be:

Case WHEN @Clients = 1 then L.Class in (2,6) else L.Class in (select distinct class from <table>) END
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 38852593
SELECT blah, blah, blah
FROM YourTable
WHERE ( (l.class IN (2, 6) AND @client = True) OR @client = False) )
0
 

Author Comment

by:BobRosas
ID: 38852826
Thank you both for your quick response.  I'm still trying to get the code to work.  

ged325
I tried the following but I can't even get it to compile.  I don't know what I'm missing...

select	workdate, empId, L.LastName + ', ' + L.FirstName AS Name, ManagerUID, 
convert(varchar(5), dateadd(second, Total_WorkDate_Units, '0:00:00'),108) as HrsMin,
Case WHEN @Clients = 1 then L.Class in (2,6) else L.Class in (select distinct class from dbo.EmployeeList) END 
from	Employee_Work_Units_Summary s inner join dbo.EmployeeList L on s.empId = L.EmployeeId
where	Total_WorkDate_Units >  32400 AND (L.Suspend = 0)  and   (ManagerUID = ISNULL(NULLIF (@Supervisor, 0), 
ManagerUID))  AND (@Employee =0 or L.EmployeeId =@Employee) 
Order by workdate, Name

Open in new window



jim horn...
I tried this ...
AND( (L.class IN (2, 6) AND @Clients = -1) OR @Clients = 0)   

Open in new window

because I have @Clients as type bit and I got an invalid error using true.  But even with that change the result is not what I expect.  
EX:
Client     Class
101              5
102              3
103             4
104              1
105              2
106               6
107               4
108                3
109                 2
110                9

For the above data, when @Clients is true(-1) I want only 2's and 6's so I should have 3 records.
When @Clients = False (0) I want all records so I'm expecting 10 records.  That's why I'm trying to not filter by Class if @Clients is False.  Maybe that's not possilbe?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38852893
clients  is true = 1 . . . not -1

AND( (L.class IN (2, 6) AND @Clients = 1) OR @Clients = 0)
0
 

Author Comment

by:BobRosas
ID: 38853011
ged325
Thank you for your input.  I made that change but my data results did not change.  Should they?
0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
ID: 38853034
try the following:

select      workdate, empId, L.LastName + ', ' + L.FirstName AS Name, ManagerUID,
convert(varchar(5), dateadd(second, Total_WorkDate_Units, '0:00:00'),108) as HrsMin
            from      Employee_Work_Units_Summary s inner join dbo.EmployeeList L on s.empId = L.EmployeeId
where      Total_WorkDate_Units >  32400 AND (L.Suspend = 0)  and   (ManagerUID = ISNULL(NULLIF (@Supervisor, 0), ManagerUID))  AND (@Employee =0 or L.EmployeeId =@Employee)
AND( (L.class IN (2, 6) AND @Clients = 1) OR @Clients = 0)

Order by workdate, L.LastName + ', ' + L.FirstName
0
 

Author Comment

by:BobRosas
ID: 38853093
Thanks again!  I copied and pasted your code right in.  It compiles and runs but if I enter 1 (true) I don't get any results (I'm expecting 1 record).  If I enter 0 (false)  it excludes class 2 and 6 and I would like to include all classes for false.  I can't figure out what I'm missing but I do appreciate your help.
0
 

Author Comment

by:BobRosas
ID: 38853186
I found it!  I was comparing a report that's been working for a long time to a newly created Report Services report (which I thought was working).  But a circumstance that I didn't code for in report services just happen to be in my test data set and I didn't see it.  Once I ran and compared other data it looked good.  I apologize and thank you both so much. I will increase points and split them.  EE is awesome!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

791 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