Reconciliation of data query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Reconciliation of data query

Author

Topic

Stubert
Starting Member

10 Posts

Posted - 2011-04-08 : 05:26:27

Hi,

I'm trying to match some data which is very esay to do with a naked eye, but I can't seem to come up with some code to do same.

Essentially, for the data below, I want to look at all records for the same reference and see if there are any values in "Amount" which add up to zero.

I've put the expected result in col 3. I guess it needs a loop to go through all possible combinations of the values, but I can't see anything that would do it.

Does any kind person have any pointers??

Thanks, Stuart

PS Its a standard finance type reconciliation thing.

Reference	Amount	Matched
123	-7.00	Y
123	1.15	N
123	6.50	Y
123	0.50	Y
ABC	200.10	Y
ABC	-150.00	Y
ABC	-50.10	Y
ABC 	12.55	N
XYZ	9.99	N

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-08 : 06:54:08

CREATE TABLE #test (Reference VARCHAR(3),Amount DECIMAL(8,2))
INSERT INTO #test
SELECT '123',-7.00 UNION ALL
SELECT '123', 1.15 UNION ALL
SELECT '123', 6.50 UNION ALL
SELECT '123', 0.50 UNION ALL
SELECT 'ABC', 200.10 UNION ALL
SELECT 'ABC', -150.00 UNION ALL
SELECT 'ABC', -50.10 UNION ALL
SELECT 'ABC', 12.55 UNION ALL
SELECT 'XYZ', 9.99

SELECT Reference,Amount,CASE RIGHT(AMOUNT,1) WHEN 0 THEN 'Y' ELSE 'N' END FROM #test

DROP TABLE #test

--------------------------
http://connectsql.blogspot.com/

Stubert
Starting Member

10 Posts

Posted - 2011-04-08 : 07:31:55

Thank you! This works great for the data sample I gave. The actual data is in a table, so I ran the same code (without the table creation) against the table.

SELECT Allocation,SAP_Amount,CASE RIGHT(SAP_Amount,1) WHEN 0 THEN 'Y' ELSE 'N' END 

FROM RIC.CustAcct_SAP_Commission_Exceptions

where Reconciliation_Period = '201103'

order by Allocation

Which gives me the following (sub-set of data)

Allocation SAP_Amount   Actual Result	Expected Result
RSAP4208731200	-71.17	         N	                      N
RSAP4208731200	-24.4	         Y	                      Y
RSAP4208731200	58.6	         Y	                      Y
RSAP4208731200	-34.2	         Y	                      Y
			
RSAP4208810200	-21.1	         Y	                      N
			
RSAP4452690200	-57.87   	 N	                      N
RSAP4452690200	-57.87	         N	                      Y
RSAP4452690200	57.87	         N	                      Y
RSAP4452690200	-2.91	         N	                      N
RSAP4452690200	0.03	         N	                      N

As you can see, where there is only one record per reference, it cannot possibly match, wheras it matches perfectly for the first reference (with 4 records). Thanks again.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-08 : 07:43:55

This may be overkill, but this is how you find all the pieces that add up to a specific value.

http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Another-bin-packaging-algorithm-using-recursion-and-XML.aspx

Jim

Everyday I learn something that somebody else already knew

Stubert
Starting Member

10 Posts

Posted - 2011-04-08 : 07:55:10

quote:
Originally posted by jimf

This may be overkill, but this is how you find all the pieces that add up to a specific value.

http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Another-bin-packaging-algorithm-using-recursion-and-XML.aspx

Jim

Everyday I learn something that somebody else already knew

Thanks, I'll take a look. There are over 2 million lines in this rec (from a SAP R3 system) so anything that stops people having to do it manually will be considered!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources