sql query to recon data between 2 systems

--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