locked
filter data based on type RRS feed

  • Question

  • Hi Team,

    Below is my requirement:

    DECLARE @TABLE TABLE
    (
    	ACCOUNTID INT,
    	PAYMENT_DATE DATETIME,
    	PAYMENT_AMNT DECIMAL(19,2),
    	PAYMENT_TYPE CHAR(1)
    )
    INSERT INTO @TABLE
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-02-05',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-03-06',100,'R'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-05-05',100,'P'
    UNION ALL
    SELECT 1,'2016-06-04',100,'P'
    UNION ALL
    SELECT 1,'2016-06-08',100,'R'
    
    --Expected output
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-05-05',100,'P'
    

    Whenever there is a record with Payment_type = 'R' then that record and also out of previous records the first record (from back - for fourth record the actual payment is 2nd record not 1st record, so 4th and 2nd record should be filtered out) with payment_type='P' and with same payment amount should be filtered out.

    Appreciate your inputs.

    Regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Tuesday, July 26, 2016 12:24 PM

Answers

  • This simple query is working for me.

    Thanks all for the inputs.

    DECLARE @TABLE TABLE
    (
    	ACCOUNTID INT,
    	PAYMENT_DATE DATE,
    	PAYMENT_AMNT DECIMAL(19,2),
    	PAYMENT_TYPE CHAR(1)
    )
    INSERT INTO @TABLE
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-02-05',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-03-06',100,'R'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-05-05',100,'P'
    UNION ALL
    SELECT 1,'2016-06-04',100,'P'
    UNION ALL
    SELECT 1,'2016-06-08',100,'R'
    
    ;WITH CTE AS 
    (
    	SELECT ROW_NUMBER() OVER(PARTITION BY ACCOUNTID ORDER BY PAYMENT_AMNT,PAYMENT_DATE) PaymentOrdering,* FROM
    	(
    	SELECT ACCOUNTID,
    	CAST(PAYMENT_DATE AS DATE) PAYMENT_DATE,
    	PAYMENT_AMNT,
    	PAYMENT_TYPE
    	FROM		
    	@TABLE
    	) E
    )
    SELECT C1.* FROM CTE C1
    WHERE NOT EXISTS (SELECT 1 FROM CTE C3 WHERE C1.PaymentOrdering = C3.PaymentOrdering - 1 AND C3.PAYMENT_TYPE = 'R')
    AND C1.PAYMENT_TYPE = 'P'
    ORDER BY C1.PAYMENT_DATE

    Regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.


    • Marked as answer by Eswararao C Wednesday, July 27, 2016 12:02 PM
    • Edited by Eswararao C Wednesday, July 27, 2016 12:03 PM
    Wednesday, July 27, 2016 12:02 PM

All replies

  • DECLARE @TABLE TABLE
    (
    	ACCOUNTID INT,
    	PAYMENT_DATE DATETIME,
    	PAYMENT_AMNT DECIMAL(19,2),
    	PAYMENT_TYPE CHAR(1)
    )
    INSERT INTO @TABLE
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-02-05',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-03-06',100,'R'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-05-05',100,'P'
    UNION ALL
    SELECT 1,'2016-06-04',100,'P'
    UNION ALL
    SELECT 1,'2016-06-08',100,'R'
    
    
    
    --select * from @TABLE
    ----Expected output
    --SELECT 1,'2016-01-01',100,'P'
    --UNION ALL
    --SELECT 1,'2016-03-01',200,'P'
    --UNION ALL
    --SELECT 1,'2016-04-05',500,'P'
    --UNION ALL
    --SELECT 1,'2016-05-05',100,'P'
    ;with mycte as (
    Select *  , Sum(case when PAYMENT_TYPE ='P' then 0 else 1 end ) Over(Partition by ACCOUNTID Order by PAYMENT_DATE ) grp from @TABLE)
    ,mycte1 as (Select * , row_number() Over(Partition by ACCOUNTID,grp  Order by  PAYMENT_DATE ) rn1 , row_number() Over(Partition by ACCOUNTID,grp  Order by  PAYMENT_DATE DESC) rn2 from mycte
    Where PAYMENT_TYPE<>'R')
    
    
    select ACCOUNTID,	PAYMENT_DATE,PAYMENT_AMNT,PAYMENT_TYPE from mycte1
    Where rn1=1 or rn2=1
    Order by ACCOUNTID, PAYMENT_DATE
     

    • Proposed as answer by Albert_ Zhang Wednesday, July 27, 2016 3:29 AM
    • Unproposed as answer by Albert_ Zhang Wednesday, July 27, 2016 5:13 AM
    Tuesday, July 26, 2016 1:33 PM
  • Hi Zhang,

    Thanks for the reply.

    Not exactly getting what i wanted last row is incorrect if there is a reversal then it should filter out matching payments from back.

    --Expected output
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-05-05',100,'P'
    
    --Your output
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-06-04',100,'P'

    regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Wednesday, July 27, 2016 5:06 AM
  • ;WITH vv AS 
    (
            SELECT *, ROW_NUMBER() OVER(PARTITION BY ACCOUNTID ORDER BY PAYMENT_DATE) AS rn 
    		FROM @TABLE WHERE PAYMENT_TYPE !='R'
    
    ), b AS 
    (
    		SELECT vv.* , SeqNo =1  from vv  WHERE rn =1 
    		UNION ALL
    		SELECT vv.*, IIF( b.PAYMENT_AMNT = vv.PAYMENT_AMNT ,b.SeqNo, b.SeqNo+1)
    		FROM  b  JOIN vv ON   b.ACCOUNTID = vv.ACCOUNTID AND b.rn +1 = vv.rn 
    )
    SELECT 	b.ACCOUNTID,b.PAYMENT_DATE,b.PAYMENT_AMNT,b.PAYMENT_TYPE
    FROM b 
    JOIN 
    	(
    	SELECT ACCOUNTID, SeqNo , MIN(rn) AS rn
    	FROM b
    	GROUP BY ACCOUNTID, SeqNo 
    	) [p] ON  p.ACCOUNTID = b.ACCOUNTID AND p.SeqNo = b.SeqNo AND p.rn = b.rn
    
    	ORDER BY 1,2


    rajivkumar.bala@yahoo.co.in

    Wednesday, July 27, 2016 6:27 AM
  • Hi Eswararao C,

    I have made a demo as following shows based on Jingyang Li’s code to group the records by using “sum(case..when..)”. After grouping, you could get the “lasted” expected record that has the same “PAYMENT_AMNT” just in the same range.

    Besides, you'd better change the data type of "PAYMENT_DATE" to "DATE", not "datetime" if you don't need the time value.

    --SELECT * FROM @TABLE
    --Select *  , Sum(case when PAYMENT_TYPE ='P' then 0 else 1 end ) Over(Partition by ACCOUNTID Order by PAYMENT_DATE ) grp from @TABLE
    ;with cte
    as
    (
    	Select *  , Sum(case when PAYMENT_TYPE ='P' then 0 else 1 end ) Over(Partition by ACCOUNTID Order by PAYMENT_DATE ) grp from @TABLE
    )
    ,ctee (ACCOUNTID, PAYMENT_DATE, PAYMENT_AMNT, PAYMENT_TYPE, grp)
    as
    (
    	select c.* from 
    	cte c
    	join 
    	(
    		select ACCOUNTID, PAYMENT_DATE, PAYMENT_AMNT, grp-1 as grp   from cte where PAYMENT_TYPE = 'R' 
    	)as t (ACCOUNTID, PAYMENT_DATE, PAYMENT_AMNT,grp)
    	on c.ACCOUNTID = t.ACCOUNTID and c.grp = t.grp and c.PAYMENT_AMNT = t.PAYMENT_AMNT
    	where c.PAYMENT_TYPE = 'P'
    )
    ,cteee
    as
    (
    	select *, RANK() over (partition by grp order by PAYMENT_DATE desc) as rk
    	from ctee 
    )
    ,result (ACCOUNTID,PAYMENTDATE)
    as
    (
    	select ACCOUNTID,PAYMENT_DATE from cteee where rk = 1
    	union all
    	select ACCOUNTID,PAYMENT_DATE from @TABLE where PAYMENT_TYPE  = 'R'
    )
    select * from @TABLE t
    where not exists (select 1 from result where result.PAYMENTDATE = t.PAYMENT_DATE and result.ACCOUNTID = t.ACCOUNTID)

    Best Regards,

    Zhang Albert

    Wednesday, July 27, 2016 7:19 AM
  • No not working.

    This is my actual data:

    I want to filter out row 2,4,6,7. If there is a reversal then that reversal and only corresponding previous payment should be filtered out.

    Appreciate your inputs.

    Regards,

    Ram.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Wednesday, July 27, 2016 10:04 AM
  • Hi Eswararao C,

    According to your above image, I think you could first make the “PAYMENT_AMNT” be positive numbers, not be negative numbers, then you could replace the “P” with “PAYMENT” and replace “R” with “PAYMENTREVERSAL” in my above code and then try again.

    Best Regards,

    Zhang Albert

    Wednesday, July 27, 2016 11:49 AM
  • This simple query is working for me.

    Thanks all for the inputs.

    DECLARE @TABLE TABLE
    (
    	ACCOUNTID INT,
    	PAYMENT_DATE DATE,
    	PAYMENT_AMNT DECIMAL(19,2),
    	PAYMENT_TYPE CHAR(1)
    )
    INSERT INTO @TABLE
    SELECT 1,'2016-01-01',100,'P'
    UNION ALL
    SELECT 1,'2016-02-05',100,'P'
    UNION ALL
    SELECT 1,'2016-03-01',200,'P'
    UNION ALL
    SELECT 1,'2016-03-06',100,'R'
    UNION ALL
    SELECT 1,'2016-04-05',500,'P'
    UNION ALL
    SELECT 1,'2016-05-05',100,'P'
    UNION ALL
    SELECT 1,'2016-06-04',100,'P'
    UNION ALL
    SELECT 1,'2016-06-08',100,'R'
    
    ;WITH CTE AS 
    (
    	SELECT ROW_NUMBER() OVER(PARTITION BY ACCOUNTID ORDER BY PAYMENT_AMNT,PAYMENT_DATE) PaymentOrdering,* FROM
    	(
    	SELECT ACCOUNTID,
    	CAST(PAYMENT_DATE AS DATE) PAYMENT_DATE,
    	PAYMENT_AMNT,
    	PAYMENT_TYPE
    	FROM		
    	@TABLE
    	) E
    )
    SELECT C1.* FROM CTE C1
    WHERE NOT EXISTS (SELECT 1 FROM CTE C3 WHERE C1.PaymentOrdering = C3.PaymentOrdering - 1 AND C3.PAYMENT_TYPE = 'R')
    AND C1.PAYMENT_TYPE = 'P'
    ORDER BY C1.PAYMENT_DATE

    Regards,

    Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.


    • Marked as answer by Eswararao C Wednesday, July 27, 2016 12:02 PM
    • Edited by Eswararao C Wednesday, July 27, 2016 12:03 PM
    Wednesday, July 27, 2016 12:02 PM