SQL Query to delete offsetting values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Query to delete offsetting values

Author

Topic

steemin
Starting Member

1 Post

Posted - 2009-05-21 : 16:16:41

Hello,

I am a novice when it comes to SQL so I was hoping someone could help me. I have a lengthy table 'datatable' which is formatted similar to the following:

account, invoice, amount
accountA, 4567, $30.00
accountA, 4567, $-30.00
accountA, 4567, $20.00
accountA, 1234, $70.00
accountA, 1234, $60.00
accountA, 1234, $-70.00
accountA, 1234, $40.00

The initial query that I run pulls the raw data into the table which will show invoices and payments. I am trying to have another MS access query that will go through the table and delete the offsetting entries when a payment matches an invoice. So in this example, both the $30 and -$30 amounts within invoice 4567 would be deleted AND the $70 and -$70 amounts within invoice 1234 would be deleted.

I have searched and one website tried the below query, but I did not have success in getting this to work correctly. Could someone provide assistance with this?

DELETE *
FROM datatable
WHERE 0 =
(SELECT SUM(amount)
FROM datatable AS t
WHERE invoice = [datatable].invoice
AND ABS(amount) = ABS([datatable].amount));

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-22 : 04:43:13

Dp ypu have anything in the table that uniquely identifies a row?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-22 : 05:03:36

HI again,

I'm not sure what kind of tools you have available in access but this is how I would do it in SQLSERVER: (Code after rest of post)

There's some things you may not have considered.

1) Your sample data only contains pairs of absolute values (so 1 copy of 30 and one of -30 for invoice 4567) -- what happens if there are 3 values? 30, 30, -30. Obviously you only want to remove 1 of the positive 30's along with the negative 30. To do that you are going to need some way to uniquely identify a row.

The code below uses a look that removes 1 set of amounts that cancel themselves out per invoice each iteration (+1) -- for the example data I've put it will loop 3 time as there are 2 sets of 30, -30 to delete and 1 30 left over.

Converting this method to one that works in access I can't help you with. Never used access.

Hope this helps a little.


DECLARE @rows INT

DECLARE @foo TABLE (
	[Id] INT
	, [accountId] INT
	, [amount] MONEY
	)

INSERT @foo
          SELECT 1, 1, -30
UNION ALL SELECT 2, 1, 30
UNION ALL SELECT 3, 1, 30
UNION ALL SELECT 4, 1, -30
UNION ALL SELECT 5, 1, 25
UNION ALL SELECT 6, 1, 256
UNION ALL SELECT 7, 2, 45
UNION ALL SELECT 8, 2, 45
UNION ALL SELECT 9, 2, -45
UNION ALL SELECT 10, 2, 25
UNION ALL SELECT 11, 2, 256
UNION ALL SELECT 12, 1, 30

SELECT * FROM @foo

SET @rows = 1
WHILE @rows > 0 BEGIN

	DELETE f
	FROM
		@foo f
		JOIN (
			SELECT
				MAX(f.[ID]) AS [fooId]
				, MAX(g.[Id]) AS [foo2Id]
			FROM
				@foo f
				JOIN @foo g ON g.[accountID] = f.[accountID] AND g.[amount] = 0 - f.[amount]
			WHERE
				f.[amount] > 0
			GROUP BY
				f.[accountID]
				, f.[amount]
			)
			del ON del.[fooId] = f.[ID] OR del.[foo2ID] = f.[Id]

	SELECT @rows = @@ROWCOUNT

END

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources