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