"Case" Statement Within "Where" Clause in SQL Server 2008

CASE statement within WHERE clause in SQL Server 2008

This might be the logic you were trying to implement. In the event that @sFRomDate be NULL or empty and SubmissionDate also be NULL or empty the record will be returned. Otherwise, the SubmissionDate will be checked to make sure it is within the range you defined.

SELECT *
FROM tbl_emp_data
WHERE (COALESCE(@sFRomDate, '') = '' AND
       COALESCE(SubmissionDate, '') = '') OR
      (COALESCE(@sFRomDate, '') <> ''     AND
       COALESCE(SubmissionDate, '') <> '' AND
       SubmissionDate BETWEEN @sFRomDate AND DATEADD(DAY, 1, @sToDate))

CASE statement for date comparison within WHERE clause in SQL Server 2008

You can easily turn it into a normal where condition instead of case :

declare 
@fromDate varchar(32)= '2015-08-04',
@toDate varchar(32) = '2016-01-04'

SELECT 
DC_MASTER.DC_NO,
PSR_LOAD_DATE_TIME,
CUS_NAME  
FROM DC_ITEM
INNER JOIN DC_MASTER ON DC_MASTER.DC_NO=DC_ITEM.DC_NO  
INNER JOIN ITEM_MASTER ON item_master.ITEM_CODE  = DC_ITEM.ITEM_CODE  
INNER JOIN  CUSTOMER_MASTER ON customer_master.CUS_CODE = DC_MASTER.CUS_CODE 
INNER JOIN LOCATION_MASTER ON location_master.LOC_CODE  = DC_ITEM.LOC_CODE 
WHERE DC_ITEM.ITEM_CODE=item_master.ITEM_CODE 
AND ((@fromDate IS NOT NULL AND @toDate IS NOT NULL and DC_MASTER.PSR_LOAD_DATE_TIME >= @fromDate
and PSR_LOAD_DATE_TIME <= @toDate) or @fromDate IS NULL or @toDate IS NULL)

Use of case statement within where clause with two columns

Based on your question, I think I have your criteria down right, but I was not quite following your UABP being zero or not zero, so you might need to change that one to fit your solution.

SELECT Max(id_num) [ID], Submission_Num FROM [Fiduciary] 
WHERE Effective_Date <= '08/1/2017' AND UABP = '' 
   OR Effective_Date <= '08/1/2017' AND UABP IS NULL 
   OR Effective_Date <= '08/1/2017' AND UABP >= 0 
OR    Effective_Date > '08/1/2017' AND UABP > 0
GROUP BY Submission_Num
ORDER BY Submission_Num

The point is, I broke it down into multiple sets of OR/AND statements.

SQL query error CASE statement in WHERE clause

You can't put conditions inside of case in most SQL dialects (including SQL Server). In general, if you're using case in a where clause at all, you're pursuing a poor solution. The right way to do this is to rewrite the whole thing using Boolean logic:

WHERE  ( @Param1 IS NOT NULL 
         AND ( @Param1 IN ( '(All)', t.column1 ) 
                OR ( @Param1 = '(Ready)' 
                     AND t.column1 NOT IN ( 'Descoped', 'Done' ) ) 
                OR ( @Param1 = '(Pending)' 
                     AND t.column1 NOT IN ( 'Descoped', 'Done' ) 
                     AND t.column2 IN ( 'Deferred', 'Rejected', 'Ready for Test' ) ) ) ) 
        OR ( @Param1 IS NULL 
             AND t.column1 IN ( 'WIP', 'Not Started' ) )

For that matter, checking is see if @Param1 is not null is kind of pointless. Since null = anything is always false, all of the conditions in the first half of the query will return false in @Param1 is null. This should accomplish what you're attempting in a more straight-forward manner:

WHERE  @Param1 IN ( '(All)', t.column1 ) 
        OR ( @Param1 = '(Ready)' 
             AND t.column1 NOT IN ( 'Descoped', 'Done' ) ) 
        OR ( @Param1 = '(Pending)' 
             AND t.column1 NOT IN ( 'Descoped', 'Done' ) 
             AND t.column2 IN ( 'Deferred', 'Rejected', 'Ready for Test' ) ) 
        OR ( @Param1 IS NULL 
             AND t.column1 IN ( 'WIP', 'Not Started' ) )

sql server 2008 case statement in where clause not working

You can join then using LEFT JOIN,

SELECT  d.*,
        COALESCE(s.Col1, e.Col1) AS Col1,
        COALESCE(s.Col2, e.Col2) AS Col2,
        COALESCE(s.Col3, e.Col3) AS Col3,
        COALESCE(s.Col4, e.Col4) AS Col4
FROM    documents d
        LEFT JOIN SR s
            ON d.relationID = d.SRID
        LEFT JOIN Events e
            ON d.relationID = e.eventID

where Col1,...., Col4 are the columns of each table youw ant to be displayed based on documenttype.

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

The more safe version of the query above assuming that the same ID can contain on SR and Events table would be by using CASE()

SELECT  d.*,
        CASE WHEN d.documenttype = 'SR' THEN s.Col1 ELSE e.Col1 END) AS Col1,
        CASE WHEN d.documenttype = 'SR' THEN s.Col2 ELSE e.Col2 END) AS Col2,
        CASE WHEN d.documenttype = 'SR' THEN s.Col3 ELSE e.Col3 END) AS Col3,
        CASE WHEN d.documenttype = 'SR' THEN s.Col4 ELSE e.Col4 END) AS Col4
FROM    documents d
        LEFT JOIN SR s
            ON d.relationID = d.SRID
        LEFT JOIN Events e
            ON d.relationID = e.eventID

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
    )

case statement in where clause - SQL Server 2008

> This is the my right solultion........now its working correctly

CREATE TABLE #Store_Id (StoreID varchar(20))

    IF @Store_Id != '0'
    BEGIN 
        INSERT INTO #Store_Id
        SELECT data FROM UDF_SplitString(@Store_Id,',')     
    END
    ELSE
        BEGIN
            INSERT INTO #Store_Id
            SELECT '0'
    END

CREATE TABLE #StoreType_Id (StoreTypeID varchar(20))

    IF @StoreType_Id != '0'
    BEGIN 
        INSERT INTO #StoreType_Id
        SELECT data FROM UDF_SplitString(@StoreType_Id,',')     
    END
    ELSE
        BEGIN
            INSERT INTO #StoreType_Id
            SELECT '0'
    END 

CREATE TABLE #Account_Id (AccountID varchar(20))

    IF @Account_Id != '0'
    BEGIN 
        INSERT INTO #Account_Id
        SELECT data FROM UDF_SplitString(@Account_Id,',')       
    END
    ELSE
        BEGIN
            INSERT INTO #Account_Id
            SELECT '0'
    END     

INSERT INTO #FinalTable(VisitDate,Merchandizer_Id,Merchandizer,MerchandizerLogin,StoreId,StoreCode,StoreName,AccountId,AccountName,
    Account_Store_Format_Id,Account_Store_Format,StoreTypeId ,StoreType ,ListId ,ListName,TimeIn ,TimeOut,PlannedDate ,Reason ,TaskCode,TotalTime)      

SELECT  Visit_Date,T.Merchandizer_ID,T.Merchandizer,Merchandizer_LoginName,STORE_ID,STORE_CODE,STORE_NAME,ACCOUNT_ID,ACCOUNT_NAME,
        Account_Store_Format_Id,Account_Store_Format,StoreType_Id, 
        StoreType,T.Listid,T.Listname,T.TimeIn,T.TimeOut,T.PlannedDate,T.Reason,TaskCode,TotalTime 
FROM [dbo].Report_RD_Coverage T
INNER JOIN #TempLocationH TL ON TL.LocationId=T.Location_Id
INNER JOIN #Store_Id on CONVERT(VARCHAR,t.Store_Id) = CASE WHEN @Store_Id = '0' THEN convert(VARCHAR,t.Store_Id) ELSE StoreID END
INNER JOIN #StoreType_Id on CONVERT(VARCHAR,t.StoreType_Id) = CASE WHEN @StoreType_Id = '0' THEN convert(VARCHAR,t.StoreType_Id) ELSE StoreTypeID END
INNER JOIN #Account_Id on CONVERT(VARCHAR,t.Account_Id) = CASE WHEN @Account_Id = '0' THEN convert(VARCHAR,t.Account_Id) ELSE AccountID END

WHERE CONVERT(Date,PDADate) Between @Start_Date AND @End_Date

"Case" Statement Within "Where" Clause in SQL Server 2008