Tuesday, October 3, 2017

Conditionally filter on a column in a WHERE clause in SQL Query

I need to apply some date filter in my query but there are some hectic behind it. What I facing in my table some dates are null and some have dates it depend upon my status like
If my status is pending so action date is empty and if somebody  take an action it insert a date.

So I summarize this I need to include my date filter on conditionally based,so what I did
first i make a variable in this I supplied a value 1 when i need this date rang filter otherwise I passed
0 in it.

DECLARE @ISChecked_Apporved_rejected_date AS VARCHAR 

SET ischecked_apporved_rejected_date ="1" 

SELECT 
       * 
FROM   table k
WHERE  k.statusid = 2 
       AND ( ( 0 = CASE 
                     WHEN ( @ISChecked_Apporved_rejected_date = '0' ) THEN 0 
                     ELSE 1 
                   END ) 
              OR ( CONVERT(NVARCHAR(10), k.approveddatetime, 111) BETWEEN 
                         COALESCE(@Approver_Rej_DateFrom, 
                         CONVERT(NVARCHAR(10), @Approver_Rej_DateFrom, 
                         111)) AND COALESCE(@Approver_Rej_DateTo, 
                                   CONVERT(NVARCHAR(10), @Approver_Rej_DateTo, 
                                   111)) ) 
           )

Short query:
Declare @StatusID as INT

set @StatusID=-1 -- it means all

DECLARE @Status_all AS VARCHAR 

 IF @StatusID =-1

 BEGIN                              

  SET @Status_all ='0'                          

    END  

   AND ( ( 0 = CASE 

                     WHEN ( @Status_all = '0' ) THEN 0 

                     ELSE 1 

                   END ) OR (Status_ID = COALESCE(@StatusID,Status_ID))) 

For Reference:
https://stackoverflow.com/questions/1978179/how-to-conditionally-filter-on-a-column-in-a-where-clause



No comments:

Post a Comment