CODEWITHSUNDEEP
sqlvbams-access
Sergiu Nastase
Sergiu Nastase

Reputation: 1

Select pair of records in same table SQL

I have a Access table called RECORDS with purchase records. 

   Id   | Type |  Price  |     Date    |  Processed  |
------------------------------------------------------
   1    |  C   |   30€   |  01/01/2016 |    Null     |
------------------------------------------------------
   2    |  R   |  -30€   |  01/01/2016 |    Null     |
------------------------------------------------------
   3    |  C   |   10€   |  02/02/2016 |    Null     |
------------------------------------------------------
   4    |  C   |   10€   |  02/02/2016 |    Null     |
------------------------------------------------------
   5    |  R   |  -10€   |  02/02/2016 |    Null     |
------------------------------------------------------
   6    |  C   |   25€   |  03/02/2016 |    Null     |
------------------------------------------------------
   7    |  C   |   42€   |  04/02/2016 |    Null     |
------------------------------------------------------

What I want to do is to set True the charge (Type 'C') records who have an opposite pair of refund record (Type 'R').

Like this: 1C and 1R = True

   Id   | Type |  Price  |     Date    |  Processed  |
------------------------------------------------------
   1    |  C   |   30€   |  01/01/2016 |    True     |
------------------------------------------------------
   2    |  R   |  -30€   |  01/01/2016 |    True     |
------------------------------------------------------
   3    |  C   |   10€   |  02/02/2016 |    True     |
------------------------------------------------------
   4    |  C   |   10€   |  02/02/2016 |    Null     |
------------------------------------------------------
   5    |  R   |  -10€   |  02/02/2016 |    True     |
-----------------------------------------------------
   6    |  C   |   25€   |  03/02/2016 |    Null     |
------------------------------------------------------
   7    |  C   |   42€   |  04/02/2016 |    Null     |
------------------------------------------------------

So far I have this but it sets True all the N charge 'C' records 

UPDATE 
RECORDS AS T1 
INNER JOIN RECORDS T2 
ON ABS(T1.Price) = ABS(T2.Price) AND T1.Date =T2.Date 
SET T1.Processed = TRUE
WHERE (T1.Type = 'C' AND T2.Type = 'R') OR (T1.Type = 'R' AND T2.Type = 'C')

Upvotes: 0

Views: 94

Answers (1)

forpas
forpas

Reputation: 164099

With EXISTS:

UPDATE RECORDS AS T 
SET T.Processed = TRUE
WHERE 
  (T.Type IN ('C', 'R') AND
    EXISTS (SELECT 1 FROM RECORDS 
      WHERE Type IN ('C', 'R') AND Type <> T.Type AND Date = T.Date AND Price + T.Price = 0
    )
  )
  AND T.Id = (SELECT MIN(ID) FROM RECORDS WHERE Date = T.Date AND Price = T.Price AND Type = T.Type)

The last condition in WHERE covers the case of duplicates in the columns Price, Date and Type.
Results:

Id  Type    Price   Date        Processed
1   C       30,00   1/1/2016    True
2   R       -30,00  1/1/2016    True
3   C       10,00   2/2/2016    True
4   C       10,00   2/2/2016    NULL
5   R       -10,00  2/2/2016    True
6   C       25,00   3/2/2016    NULL
7   C       42,00   4/2/2016    NULL

Upvotes: 1

Related Questions