I have a scenario where I need to select data based on month-end.

Raw data looks like:

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.

Desired Output:

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!