How to Write the Equivalent SQL Case Statement for Query Given Below

How to write the equivalent SQL case statement for query given below?

SELECT        Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
    FROM            Customer_New
        where        (CASE
            WHEN @Clients_Title != ''   THEN Clients_Title=@Clients_Title
          ELSE
             NULL IS NULL
          END)
          AND(CASE
            WHEN @Address_Current != '' THEN Address_Current =@Address_Current
          ELSE
             NULL IS NULL
          END)
                  AND(CASE
            WHEN @Phone_Number != '' THEN Phone_Number=@Phone_Number
          ELSE
             NULL IS NULL
          END)
          AND(CASE
            WHEN @Mobile_Number != '' THEN Mobile_Number=@Mobile_Number
          ELSE
             NULL IS NULL
          END)
          AND(CASE
            WHEN @AreaLocation != '' THEN AreaLocation =@AreaLocation
          ELSE
             NULL IS NULL
          END)

How can I write an sql query for the following scenario?

The correct answer is :

SELECT        Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
    FROM            Customer_New
        where        (CASE
            WHEN @Clients_Title != ''   THEN Clients_Title=@Clients_Title
          ELSE
             NULL IS NULL
          END)
          AND(CASE
            WHEN @Address_Current != '' THEN Address_Current =@Address_Current
          ELSE
             NULL IS NULL
          END)
                  AND(CASE
            WHEN @Phone_Number != '' THEN Phone_Number=@Phone_Number
          ELSE
             NULL IS NULL
          END)
          AND(CASE
            WHEN @Mobile_Number != '' THEN Mobile_Number=@Mobile_Number
          ELSE
             NULL IS NULL
          END)
          AND(CASE
            WHEN @AreaLocation != '' THEN AreaLocation =@AreaLocation
          ELSE
             NULL IS NULL
          END)

case statement in where clause - SQL Server

You don't need case in the where statement, just use parentheses and or:

Select * From Times
WHERE StartDate <= @Date AND EndDate >= @Date
AND (
    (@day = 'Monday' AND Monday = 1)
    OR (@day = 'Tuesday' AND Tuesday = 1)
    OR Wednesday = 1
)

Additionally, your syntax is wrong for a case. It doesn't append things to the string--it returns a single value. You'd want something like this, if you were actually going to use a case statement (which you shouldn't):

Select * From Times
WHERE (StartDate <= @Date) AND (EndDate >= @Date)
AND 1 = CASE WHEN @day = 'Monday' THEN Monday
             WHEN @day = 'Tuesday' THEN Tuesday
             ELSE Wednesday
        END 

And just for an extra umph, you can use the between operator for your date:

where @Date between StartDate and EndDate

Making your final query:

select
    * 
from 
    Times
where
    @Date between StartDate and EndDate
    and (
        (@day = 'Monday' and Monday = 1)
        or (@day = 'Tuesday' and Tuesday = 1)
        or Wednesday = 1
    )

Alternative to parameter for case statement

So instead of using the case statement with the parameter, use it as your filter. So your view would be something like (best practice is to always include schema on your objects, I assumed dbo):

view

select warehouse
     , productStatusId
     , productId
from dbo.products p
left join dbo.productstatus s on p.availability = s.cd;

query to use view

select productId
 , productStatusId
from schema.viewname
where warehouse = @warehouse;

another way to get all warehouse statuses

select cast(case when warehouse = @warehouse then 1 else 0 end as bit) as AtThisWarehouse, 
productStatusId
from schema.viewname;

How to improve a case statement that uses two columns

You could do it this way:

-- Notice how STATE got moved inside the condition:
CASE WHEN STATE = 2 AND RetailerProcessType IN (1, 2) THEN '"AUTHORISED"'
     WHEN STATE = 1 AND RetailerProcessType = 2 THEN '"PENDING"'
     ELSE '"DECLINED"'
END

The reason you can do an AND here is that you are not checking the CASE of STATE, but instead you are CASING Conditions.

The key part here is that the STATE condition is a part of the WHEN.

SQL Case Expression Syntax?

The complete syntax depends on the database engine you're working with:

For SQL Server:

CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

or:

CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

expressions, etc:

case-expression    - something that produces a value
when-expression-x  - something that is compared against the case-expression
value-1            - the result of the CASE statement if:
                         the when-expression == case-expression
                      OR the boolean-when-expression == TRUE
boolean-when-exp.. - something that produces a TRUE/FALSE answer

Link: CASE (Transact-SQL)

Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.

Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL.

SELECT query with CASE condition and SUM()

Select SUM(CASE When CPayment='Cash' Then CAmount Else 0 End ) as CashPaymentAmount,
       SUM(CASE When CPayment='Check' Then CAmount Else 0 End ) as CheckPaymentAmount
from TableOrderPayment
Where ( CPayment='Cash' Or CPayment='Check' ) AND CDate<=SYSDATETIME() and CStatus='Active';

Set multiple variables and case statement in 1 query

You can try a pivot table. The first part of the query is just creating temp tables to hold the data.

/*CREATE TEMP TABLES*/
DECLARE @a TABLE ( lot int, tid int)
DECLARE @b TABLE ( lot int, id int)
DECLARE @t TABLE ( id int, flag VARCHAR(1))

INSERT INTO @a (lot, tid) VALUES (100, 1) , (100, 2)
INSERT INTO @b (lot, id) VALUES (100, 123)
INSERT INTO @t (id, flag) VALUES (1, 'Y') , (2, 'N')

/*QUERY*/
SELECT      [Y] [@id] ,
            [N] [@id_not_flag]
FROM (
SELECT      a.lot, a.tid, b.id , t.flag
            --,ID = (CASE WHEN flag = 'Y' THEN  a.tid END)
            --,ID_Not_Flag = (CASE WHEN flag <> 'Y' THEN a.tid END)
FROM        @a a
LEFT JOIN   @b b
                ON a.lot = b.lot
LEFT JOIN   @t t
                ON t.id = a.tid) AS Src
PIVOT (max(Src.tid) FOR flag in ([Y] , [N])) Pvt

---

Related Topics