sql query to recon data between 2 systems

Question

--SQL query for getting data from 2 vendor systems

 

--Hi All,

--I get data from 2 vendor systems. I need to reconcile this data and match. I have very limited fields to compare

 

DECLARE @vendor1 TABLE

                (

                                Amount money,                              

                                cspID varchar(12),

                                VName varchar(20),

                                Fund varchar(8),

                                SDate varchar(50),        

                                SettleLocation varchar(3),                        

                                TranID varchar(20),                       

                                Tradedate varchar(50)

                )

 

INSERT INTO @vendor1

SELECT 4986474.00,    '16677JX27', 'BID', '100', '20250327',      'DTT', '863233', '20250327'

UNION ALL

SELECT 97774000.00, '16677JX27',      'BID',      '100',     '20250327',        'DTT','863137',   '20250326'

UNION ALL

SELECT 24925000.00, '93114ERM1',    'MIZUH',              '100',     '20250327','DTT','863241',            '20250327'

UNION ALL

SELECT 74775260.42, '93114ERM1',    'BARC', '100',     '20250327','DTT','863237',            '20250327'

 

SELECT * FROM @vendor1

 

DECLARE @vendor2 TABLE

                (

                                vAmount money,                            

                                vcspID varchar(12),                      

                                SDate varchar(50)

                )

 

INSERT INTO @vendor2

SELECT                4986474.00,     '16677JX27', '20250327'

UNION ALL

SELECT                48887000.00,   '16677JX27', '20250327'

UNION ALL

SELECT                48887000.00,   '16677JX27', '20250327'

UNION ALL

SELECT                24925000.00,   '93114ERM1', '20250327'

UNION ALL

SELECT                24925086.81,   '93114ERM1', '20250327'

UNION ALL

SELECT                49850173.61,   '93114ERM1', '20250327'

 

SELECT * from @vendor2

 

If you notice, vendor1 has only 2 rows for 16677JX27 where as vendor2 has 3 rows

For 16677JX27, 4986474.00 amount is same in both.

where as  in Vendor1 row 2 has 97774000.00 for 16677JX27

 

This same 16677JX27 in vendor2 has 2 rows with 2 amounts (48887000.00 + 48887000.00)

 

How do I do one one to one match and then 1 - to many.

 

Vendor1 final output table should have

Vendor2 final output should have

In summary vendor 1 can have 1 transaction which can be sum of multiple transactions in vendor 2.

Vice versa can also happen.

Multiple transactions in vendor 1 and only one in Vendor 2.

How do I achieve this because there are no matching columns between 2 systems other than sDate and cspID

Thank you

 

Answer 1

It seems that the expected results for the tables are:

select *
from @vendor1
where cspID = '16677JX27'
order by cspID

select *
from @vendor2 v2
where vCspID = '16677JX27' and vAmount in (select Amount from @vendor1 where cspID = v2.vcspID)
union all
select sum(vAmount) as vAmount, vcspID, SDate
from @vendor2 v2
where vCspID = '16677JX27' and vAmount not in (select Amount from @vendor1 where cspID = v2.vcspID)
group by vcspID, SDate
having sum(vAmount) in (select Amount from @vendor1 where cspID = v2.vcspID)
order by vcspID

The cspID = '16677JX27' conditions can be removed to include all items.

If “1-to-many” is possible between @ventor2 and @vendor1 too, then show an example and expected results for @vendor1.

Answer 2

Why match individual transactions? Say there are 5 transactions, but vendor 1 reduced to 3, and vendor 2 reduced to 4. There may be no matches. I’d sum by capid and compare.