generate flags in the table after treatment

[giovanneb]

Hello community, I need help on this issue below

ID_DOC	ID_CD_MOV	DATE	VL	FLAG
80425890	10	1/1/2012	1500
80425890	10	2/1/2012	1500
80425890	10	4/1/2012	1500	x
80425890	10	4/1/2012	1700	x
80425890	11	4/1/2012	-1500	x
80425890	11	4/1/2012	-1700	x
80425890	10	4/2/2012	1500
80425890	10	4/2/2012	1700
1006350	10	5/1/2012	1500	x
1006350	11	5/1/2012	-1500	x
1006350	10	5/2/2012	1500

I have a table of payments, where I have a table of payments, where ID_CD_MOV = 10 (Payments done) and ID_CD_MOV = 11 (Payments reversed).

I have a table of payments, where my problem must disregard the payments reversed and disregard previous payments related to the reversal, as they have done again after receiving the cancelation

The table clearly shows what I need, the flag field does not exist in the database, just put as a reference to the need

Thanks

[Gysbert_Wassenaar]

This reversal doesn't have a matching payment afaict.

80425890

11

4/1/2012

-1200

x

If -1200 should be -1700 then I can match it. See attached example.

[giovanneb]

-1200 I have not had time to assemble wrong in the example, you are correct in that case held that the dates are the same at the time of making the comparison and if the dates are different example:

ID_DOC	ID_CD_MOV	DATE	VL	FLAG
80425895	10	01/01/2012	1000
80425895	10	02/01/2012	1000
80425895	10	04/01/2012	1000	x
80425895	10	04/01/2012	1200	x
80425895	11	05/01/2012	-1000	x
80425895	11	05/01/2012	-1200	x
80425895	10	07/02/2012	1000
80425895	10	07/02/2012	1200
1006370	10	05/01/2012	1000	x
1006370	11	05/01/2012	-1000	x
1006370	10	08/02/2012	1000

[calvindk · Accepted solution]

I worked a bit of off the examples provided and this one works in this specific case.

However the arbitrary selection of what to match is causing me some trouble, as well as the possibility of having multiple same value negatives per id.

Might be a starting point for a real solution.

Best wishes