Answered by:
filter data based on type
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,'20160101',100,'P' UNION ALL SELECT 1,'20160205',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160306',100,'R' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',100,'P' UNION ALL SELECT 1,'20160604',100,'P' UNION ALL SELECT 1,'20160608',100,'R' Expected output SELECT 1,'20160101',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',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,'20160101',100,'P' UNION ALL SELECT 1,'20160205',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160306',100,'R' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',100,'P' UNION ALL SELECT 1,'20160604',100,'P' UNION ALL SELECT 1,'20160608',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,'20160101',100,'P' UNION ALL SELECT 1,'20160205',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160306',100,'R' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',100,'P' UNION ALL SELECT 1,'20160604',100,'P' UNION ALL SELECT 1,'20160608',100,'R' select * from @TABLE Expected output SELECT 1,'20160101',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',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,'20160101',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',100,'P' Your output SELECT 1,'20160101',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160604',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, grp1 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
 Edited by Albert_ Zhang Wednesday, July 27, 2016 8:04 AM
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,'20160101',100,'P' UNION ALL SELECT 1,'20160205',100,'P' UNION ALL SELECT 1,'20160301',200,'P' UNION ALL SELECT 1,'20160306',100,'R' UNION ALL SELECT 1,'20160405',500,'P' UNION ALL SELECT 1,'20160505',100,'P' UNION ALL SELECT 1,'20160604',100,'P' UNION ALL SELECT 1,'20160608',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