SQL add to WHERE clause based on condition

Question

How can I add a condition inside the WHERE CLAUSE based on a case or if condition?

SELECT C.CallID, A.GroupName,A.Assignee, C.CallStatus, C.RecvdDate, C.Urgency, C.Category, C.CallType, C.KPIreport, C.CallDesc,
DATEDIFF(DAY, C.RecvdDate,GETDATE()) [DurationInDays],
CASE 
    WHEN DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 30 
        THEN 'Less than 30'
    WHEN DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 30 AND DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 60  
        THEN '30-59'
    WHEN DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 60 AND DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 90  
        THEN '60-89'
    WHEN DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 90 AND DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 120  
        THEN '90-119'
    WHEN DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 120 
        THEN 'Over 120'
END 

AS 'AgeClassification'

FROM
    CallLog C
    INNER JOIN Asgnmnt A ON C.CallID = A.CallID
WHERE
    A.HEATSeq =(SELECT MAX(HEATSeq)FROM Asgnmnt WHERE (CallID = C.CallID))
    AND UPPER(A.GroupName) = @GroupName
    AND LOWER(A.EMail) IN (@Assignee)
    AND UPPER(C.CallStatus) = @RecordType

    AND 
    CASE WHEN UPPER(C.CallStatus) = 'CLOSED' THEN
            C.ClosedDate >= @StartDate AND C.ClosedDate <= @EndDate
         WHEN UPPER(C.CallStatus) = 'REQUEST TO CLOSE' THEN
            A.DateResolv >= @StartDate AND A.DateResolv <=@EndDate
    END




ORDER BY A.GroupName, A.Assignee,  C.RecvdDate, C.CallID

My logic here is that, when the @RecordType is 'CLOSED', the WHERE clause must validate the closed date and if the @RecordType is 'REQUEST TO CLOSE' then it must validate the resolved date.

I also tried doing this using IF statement but I get the same error.

Where column1 = case when condition then value else column1 end. Do this for both cases. — S3S, Apr 27 '17 at 03:01
I only want to include in where clause if the condition is met — aronccs, Apr 27 '17 at 03:20
Whats the problem with this query? You appear to have implemented your requirements correctly. — Nick.Mc, Apr 27 '17 at 03:24

score 2 · Accepted Answer · edited May 23 '17 at 12:25

Please try the following...

SELECT C.CallID,
       A.GroupName,
       A.Assignee,
       C.CallStatus,
       C.RecvdDate,
       C.Urgency,
       C.Category,
       C.CallType,
       C.KPIreport,
       C.CallDesc,
       DATEDIFF( DAY,
                 C.RecvdDate,
                 GETDATE() ) AS DurationInDays,
       CASE
           WHEN DATEDIFF( DAY,
                          C.RecvdDate,
                          GETDATE() ) < 30 THEN
               'Less than 30'
           WHEN DATEDIFF( DAY,
                          C.RecvdDate,
                          GETDATE() ) BETWEEN 30 AND 59
               '30-59'
           WHEN DATEDIFF( DAY,
                          C.RecvdDate,
                          GETDATE() ) BETWEEN 60 AND 89
               '60-89'
           WHEN DATEDIFF( DAY,
                          C.RecvdDate,
                          GETDATE() ) BETWEEN 90 AND 119
               '90-119'
           WHEN DATEDIFF( DAY,
                          C.RecvdDate,
                          GETDATE() ) >= 120 THEN
               '120 or over'
       END AS 'AgeClassification'
FROM
    CallLog C
    INNER JOIN Asgnmnt A ON C.CallID = A.CallID
WHERE
    A.HEATSeq =( SELECT MAX( HEATSeq )
                 FROM Asgnmnt
                 WHERE ( CallID = C.CallID ) )
    AND UPPER( A.GroupName ) = @GroupName
    AND LOWER( A.EMail ) IN ( @Assignee )
    AND UPPER( C.CallStatus ) = @RecordType
    AND ( ( @RecordType = 'CLOSED' AND
            C.ClosedDate BETWEEN @StartDate AND @EndDate ) OR
           ( @RecordType = 'REQUEST TO CLOSE' AND
             A.DateResolv BETWEEN @StartDate AND @EndDate ) OR
           ( @RecordType <> 'CLOSED' AND
             @RecordType <> 'REQUEST TO CLOSE' )
        );

I have accepted the first four conditions from your WHERE clause. Instead of using a CASE for the fifth condition I have instead chosen the equivalent form using AND, OR and round brackets. The first subcondition @RecordType = 'CLOSED' AND C.ClosedDate BETWEEN @StartDate AND @EndDate is the equivalent of your first WHEN. The second subcondition @RecordType = 'REQUEST TO CLOSE' AND A.DateResolv BETWEEN @StartDate AND @EndDate ) is the equivalent of your second WHEN. The third subcondition will allow all records that meet the other criteria where @RecordType is neither CLOSED nor REQUEST TO CLOSE. You should remove the third subcondition if you would rather return an empty set in those circumstances.

From your fourth WHERE condition, where UPPER( C.CallStatus ) equals either CLOSED or REQUEST TO CLOSE then so will @RecordType. Thus I have replaced UPPER( C.CallStatus ) with @RecordType in your fifth condition. In addition to being slightly more readable, this also eliminates four function calls from the statement, making it more efficient.

I have also modified your CASE from early in the statement to equivalent conditions that use less function calls, are (arguably) easier to read, and that more clearly parallel your field aliases.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Further Reading

"CASE" statement within "WHERE" clause in SQL Server 2008

Ikyong · Answer 2 · 2017-04-27T03:38:18.897

SELECT C.CallID, A.GroupName,A.Assignee, C.CallStatus, C.RecvdDate, 
       C.Urgency, C.Category, C.CallType, C.KPIreport, C.CallDesc,
       DATEDIFF(DAY, C.RecvdDate,GETDATE()) [DurationInDays],
       CASE 
          WHEN (DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 30) THEN 'Less than 30'
          WHEN (DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 30 AND DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 60) THEN '30-59'
          WHEN (DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 60 AND DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 90) THEN '60-89'
          WHEN (DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 90 AND DATEDIFF(DAY, C.RecvdDate,GETDATE()) < 120) THEN '90-119'
          WHEN (DATEDIFF(DAY, C.RecvdDate,GETDATE()) >= 120)  THEN 'Over 120'
       END  AS 'AgeClassification'
FROM CallLog C
INNER JOIN Asgnmnt A ON C.CallID = A.CallID
WHERE A.HEATSeq =(SELECT MAX(HEATSeq)FROM Asgnmnt WHERE (CallID = C.CallID))
AND UPPER(A.GroupName) = @GroupName
AND LOWER(A.EMail) IN (@Assignee)
AND UPPER(C.CallStatus) = @RecordType
AND (CASE 
    WHEN UPPER(C.CallStatus) = 'CLOSED' THEN C.ClosedDate
    WHEN UPPER(C.CallStatus) = 'REQUEST TO CLOSE' THEN A.DateResolv
END) BETWEEN @StartDate AND @EndDate
ORDER BY A.GroupName, A.Assignee,  C.RecvdDate, C.CallID

score 0 · Answer 3 · answered Apr 27 '17 at 03:22

You could try the following

SELECT C.CallID, 
       A.GroupName, 
       A.Assignee, 
       C.CallStatus, 
       C.RecvdDate, 
       C.Urgency, 
       C.Category, 
       C.CallType, 
       C.KPIreport, 
       C.CallDesc, 
       Datediff(DAY, C.RecvdDate, Getdate()) [DurationInDays], 
       CASE 
         WHEN Datediff(DAY, C.RecvdDate, Getdate()) < 30 THEN 'Less than 30' 
         WHEN Datediff(DAY, C.RecvdDate, Getdate()) >= 30 
              AND Datediff(DAY, C.RecvdDate, Getdate()) < 60 THEN '30-59' 
         WHEN Datediff(DAY, C.RecvdDate, Getdate()) >= 60 
              AND Datediff(DAY, C.RecvdDate, Getdate()) < 90 THEN '60-89' 
         WHEN Datediff(DAY, C.RecvdDate, Getdate()) >= 90 
              AND Datediff(DAY, C.RecvdDate, Getdate()) < 120 THEN '90-119' 
         WHEN Datediff(DAY, C.RecvdDate, Getdate()) >= 120 THEN 'Over 120' 
       END                                   AS 'AgeClassification' 
FROM   CallLog C 
       INNER JOIN Asgnmnt A 
               ON C.CallID = A.CallID 
WHERE  A.HEATSeq = (SELECT Max(HEATSeq) 
                    FROM   Asgnmnt 
                    WHERE  ( CallID = C.CallID )) 
       AND Upper(A.GroupName) = @GroupName 
       AND Lower(A.EMail) IN ( @Assignee ) 
       AND Upper(C.CallStatus) = @RecordType 
       AND C.ClosedDate BETWEEN IIF(Upper(C.CallStatus) = 'CLOSED', @StartDate, C.ClosedDate) 
                        AND IIF(Upper(C.CallStatus) = 'CLOSED', @EndDate, C.ClosedDate) 
       AND C.DateResolv BETWEEN IIF(Upper(C.CallStatus) = 'REQUEST TO CLOSE', @StartDate, C.DateResolv) 
                        AND IIF(Upper(C.CallStatus) = 'REQUEST TO CLOSE',@EndDate, C.DateResolv) 
ORDER  BY A.GroupName, 
          A.Assignee, 
          C.RecvdDate, 
          C.CallID

SQL add to WHERE clause based on condition

3 Answers3