I create a report based on values passed from a front end. The user selects the statuses they want to see from checkboxes. The user can select a few different statuses.They can either select All, Pending, InReview, Cleared. If they select all I display all records. If they select cleared and pending I need to display only cleared and pending records.

These are the variables being passed:
@sStatusAll (0 If not selected, 1 if selected)
@sStatusPending (0 If not selected, 1 if selected)
@sStatusInReview (0 If not selected, 1 if selected)
@sStatusCleared (0 If not selected, 1 if selected)

In this case I would have to look at:
sStatusAll =0
sStatusPending =1
sStatusInReview =0
sStatusCleared =1

Based on these I need to produce the report to display only the pending and cleared records.

I am having trouble coming up with the query. Can anyone help?

Thanks,
Ninel


[Code]
CREATE TABLE reCUSTOMER (
iID int IDENTITY (1, 1) NOT NULL ,
sCustName varchar(50) NULL,
sSIM varchar(20) NULL,
sPlanCode varchar(20) NULL,
iStatus int NULL)

INSERT reCUSTOMER (sCustName, sSIM, sPlanCode, iStatus )
VALUES('John Smith', '123', 'ABC', 'Pending')
INSERT reCUSTOMER (sCustName, sSIM, sPlanCode, iStatus )
VALUES('Jane Smith', '246', 'DEF', 'InReview')
INSERT reCUSTOMER (sCustName, sSIM, sPlanCode, iStatus )
VALUES('Joan Smith', '789', 'GHI', 'Cleared')
[code]

I believe I answered the same question somewhere else. Here's the query:

SELECT * FROM reCustomer
WHERE @sStatusAll = 1 OR (@sStatusPending = 1 AND iStatus = 'Pending') OR
(@sStatusInReview = 1 AND iStatus = 'InReview') OR
(@sStatusCleared = 1 AND iStatus = 'Cleared')

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

Thank you for your help. I really appreciate it.