AND/OR Logic

  • 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.

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

    Thanks,                                                                                                                                                                         Ninel

    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')

     

  • One more thing I forgot to add.... 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 =1

    sStatusCleared =0

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

     

  • There are many ways of accomplishing it, but the easiest is probably to have a parameter for Pending, InReview, and Cleared, passing 0 for not checked or 1 for checked. If the user checks All, simply pass a 1 for all of them. Other options are using bits for each checkbox, a delimited string of options that were checked, which you could parse, or using dynamic sql, integrate into an IN(), etc., but the first one should serve you well enough. If you go that route, then the WHERE clause would look something like (untested):

    WHERE

     iStatus = CASE WHEN @sStatusAll = 1 THEN iStatus ELSE '' END OR

     iStatus = CASE WHEN @sStatusPending = 1 THEN 'Pending' ELSE '' END OR

     iStatus = CASE WHEN @sStatusInReview = 1 THEN 'InReview' ELSE '' END OR

     iStatus = CASE WHEN @sStatusCleared = 1 THEN 'Cleared' ELSE '' END

    Edited to account for the additional info you posted prior to me submitting my response.

  • You might find this article both interesting and helpful.

    http://www.sommarskog.se/dyn-search.html#Umachandar

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Hi,

    I've noticed that in your table DDL you've created the iStatus column as INT but the INSERT statements have VARCHAR values for the iStatus column. If iStatus is VARCHAR the following should work:

    SELECT *

    FROM reCUSTOMER

    WHERE iStatus IN(CASE @sStatusPending WHEN 1 THEN 'Pending' END,

       CASE @sStatusInReview WHEN 1 THEN 'InReview' END,

       CASE @sStatusCleared WHEN 1 THEN 'Cleared' END)

    OR @sStatusAll = 1

    Ade



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply