cutmancometh cutmancometh - 7 months ago 11
SQL Question

SQL Find Corresponding Negative and Positive Records

We have an accounting database implemented in SQL Server. The actual accounting records are int the

records
table with the following schema (names changed to protect the innocent + removed irrelevant columns).

`recordId` INT PRIMARY KEY
`amount` MONEY
`dateRecorded` DATE
`campaignYear` INT
`storeId` INT FOREIGN KEY (to the stores table)
`productId` INT FOREIGN KEY (to the products table)


Some bad records were added. Business rules prevented us from simply deleting the records, so they had to be zeroed out with negative records for the same amount (i.e. a $4,521 record gets zeroed out with a -$4,521 record).

The problem is that some of the negative records got tied to an incorrect productId.

I need a query that shows all records with opposite amounts, within the same campaignYear, that are tied to different productIds.

The result set would look something like this:

recordId | amount | dateRecorded | campaignYear | storeId | productId
11545 | 1132.13 | '2015-05-14' | 2015 | 45 | 1729
90463 | -1132.13 | '2015-08-02' | 2015 | 45 | 2402
25487 | 9300.00 | '2011-01-13' | 2010 | 122 | 85060
67953 | -9300.00 | '2014-06-06' | 2010 | 122 | 11348
01045 | 5045.99 | '2001-11-29' | 2001 | 3 | 105
32468 | -5045.99 | '2016-08-01' | 2001 | 3 | 109


Notice the same storeId but different productId for each pair of positive/negative records.

I don't really know how to go about this.

Thanks in advance!

vkp vkp
Answer

Using exists.

select * from tablename t
where exists (select 1 from tablename t1
              where t.amount = -1*t1.amount 
              and t.productid <> t1.productid 
              and t.campaignyear = t1.campaignyear
              and t.storeid = t1.storeid)

Using self-join.

select t.* 
from tablename t
JOIN tablename t1 ON t.productid <> t1.productid 
and t.campaignyear = t1.campaignyear
and t.storeid = t1.storeid
WHERE t.amount = -t1.amount
ORDER BY t.storeid, abs(t.amount) 
Comments