• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Select records based on boolean parameter

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
BobRosas
Asked:
BobRosas
  • 4
  • 3
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT blah, blah, blah
FROM YourTable
WHERE ( (l.class IN (2, 6) AND @client = True) OR @client = False) )
0
 
BobRosasAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
clients  is true = 1 . . . not -1

AND( (L.class IN (2, 6) AND @Clients = 1) OR @Clients = 0)
0
 
BobRosasAuthor Commented:
ged325
Thank you for your input.  I made that change but my data results did not change.  Should they?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
BobRosasAuthor Commented:
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
 
BobRosasAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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