I have a scenario where I need to select data based on month-end.
Raw data looks like:
ID | Date | Cost | IS_REVERSED | Reverse_ID |
---|---|---|---|---|
1 | 2021-01-01 | $1 | No | NULL |
2 | 2021-01-30 | $2 | YES | NULL |
3 | 2021-02-01 | $3 | NULL | 2 |
4 | 2021-02-03 | $4 | No | NULL |
Please note the IS_REVERSED
flag column and Reverse_ID
column. If the transaction is successful in the first attempt the flag is NO, But if the transaction is successful in the second attempt the flag is NULL
My desired output if I run the report for January end it should bring all transactions that happened in Jan (even if it reversed is Yes but reversal has not yet happened)
ID 1, 2
For next month-end, I need to report data from Jan and Feb combined. and the desired output should be
ID 1, 3 and 4
Id 2 should not be reported in because that has a reverse flag of Yes
Any pointers to achieve this would be much appreciated.
Create table Test_Report
(ID Int
,[Date] date
,Cost varchar(100)
,Is_reversed varchar(100)
,Reversed_ID int)
insert into Test_Report values
(1 ,'2021-01-01', '$1' , 'No', NULL),
(2 ,'2021-01-30', '$2' , 'YES', NULL),
(3 ,'2021-02-01', '$3' , NULL, 2),
(4 ,'2021-02-03', '$4' , 'No', NULL)
I need a single query where i can pass [date] as a condition to filter out records. (date < '2021-01-31' should bring id 1,2 ) (date <'2021-02-28' should bring id 1,3,4) ID 2 should not come as the transaction is reversed and we have a new transaction (Id 3) with IS_REVERSE flag as NULL.
Thanks
CodePudding user response:
I used a dynamic SQL query to achieve the results. Since your data is not clear and I see the datatypes are not correct, assumptions are used. So, DML and DDL are included with the answer
Create table Test_Report
(ID Int
,[Date] varchar(100)
,Cost varchar(100)
,Is_reversed varchar(100)
,Reversed_ID int)
insert into Test_Report values (1 ,'01 Jan', '$1' , 'No', NULL)
,(1 ,'30 Jan', '$2' , 'YES', NULL)
,(1 ,'01 Feb', '$3' , NULL, 2)
,(1 ,'03 Feb', '$4' , 'No', NULL)
declare @from varchar(100) = 'Jan'
, @to varchar(100) = 'Feb'
declare @where varchar(max)
declare @query varchar(max)
set @query = 'select * from Test_Report where '
if @from = @to
begin
set @where = ' [Date] like ''%' @from '%'' and ( isnull(Is_reversed,'''') in (''No'', ''YES'', '''')) and Reversed_ID is null'
end
else
begin
set @where = ' ( [Date] like ''%' @from '%'' or [Date] like ''%' @to '%'' ) and ( isnull(Is_reversed,'''') in (''No'', ''''))'
end
Declare @Main varchar(max) = @query @where
--This statement can be used to test the resulted query
--select @Main
exec(@main)
CodePudding user response:
I am assuming you need to impute the date of the report you wish to run. In this case I would declare a variable for your WHERE clause.
This variable outputs the current date, but you can replace the GETDATE() syntax with a date value to make the query dynamic. Now the WHERE clause...
DECLARE @DATEVAR AS DATE = GETDATE()
SELECT *
,EOMONTH(#Test_Report.[Date],0) [EOMONTH for WHERE clause]
FROM #Test_Report
WHERE (Is_reversed != 'YES'
OR Is_reversed IS NULL)
AND #Test_Report.[Date] <= EOMONTH(@DATEVAR,0)
The EOMONTH() function can be used here to evaluate the end of the month selected, and select all records less than that month-end date. Since you want both records where Is_reversed is not YES and is NULL, we need to put an OR clause in parenthesis.
A more clever way to deal with the nulls is the ISNULL(,) function. You can use it in your WHERE clause to make that constraint a 1-liner.
SELECT *
,EOMONTH(#Test_Report.[Date],0) [EOMONTH for WHERE clause]
FROM #Test_Report
WHERE ISNULL(Is_reversed,'No') != 'YES'
AND #Test_Report.[Date] <= EOMONTH(@DATEVAR,0)
Either method should do the trick!