Match two data sets based on item values

Transact-SQL
1

I have two data frames, which i want to combine usinga left join or similar.
PV number wise subtotal of the amount column of the dataframe (Pmt) has to be match with the second table (BankWithdrawal).
If such sub total matches, then join such row of with the 'BankWithdrawal' table.

Pmt Table

PVNo. Description Date ChqNo Amount
285922 ATL/16/154,151 PAYMENT FOR BRAN 01-04-16 250512 10404.00
285922 ATL/16/154,151 PAYMENT FOR BRAN 01-04-16 250512 16418.67
285904 PAYMENT FOR BRANCH RENT 01-05-16 900000.00
285912 SE14110206 OD WAVE OFF 10-05-16 250523 47250.00
285944 100P1605 - se vq fd chq 2016-4-4 01-07-16 400000.00
285942 FD WD PR 4970 10-07-16 533957 12154.25
285893 MAT WD PR 4963 15-07-16 176954.25
285893 MAT WD PR 4963 15-07-16 23045.75

Bank Withdrawal Table

B.Date |B.Description |B.ChqNo |B.Amount |
01-04-16 |Chq |250512 |26822.67 |
01-05-16 |Direct | |900000.00 |
20-05-16 |Chq |250523 |47250.00 |
30-06-16 |Other Chgs | |5000.00 |
20-07-16 |Direct | |200000.00 |

SQL code i tried

SELECT *
INTO [expectedTable]
FROM [pmt] p
inner join [Withdrawal] b
WHERE (SELECT sum(p.Amount) as 'PmtAmount'
FROM [pmt] p
GROUP BY p.PVNo) = b.[Withdrawals];

Error-
Incorrect syntax near the keword 'WHERE'

Can someone help me to get the correct code?
Thank you

2

please provide sample data as follows

create table #Pmt(PVNo int, Description varchar(50), Date date, ChqNo int, Amount money)

insert into #Pmt
select 285922, 'ATL/', 01-04-16, 250512, 10404.00

etc

3

CREATE TABLE[Test].[dbo].[pmt] (
[PVNo] float,
[Description] nvarchar(255),
[Date] datetime,
[ChqNo] float,
[Amount] float
)

INSERT INTO [Test].[dbo].[pmt]
([PVNo]
,[Description]
,[Date]
,[ChqNo]
,[Amount])
VALUES
(285922,'ATL/16/154,151 PAYMENT FOR BRAN','2016-04-01',250512,10404)
,(285922,'ATL/16/154,151 PAYMENT FOR BRAN','2016-04-01',250512,16418.67)
,(285904,'PAYMENT FOR BRANCH RENT', '2016-05-01',NULL,900000)
,(285912,'SE14110206 OD WAVE OFF', '2016-05-10',250523,47250)
,(285944,'100P1605 - se vq fd chq', '2016-07-01' ,NULL,400000)
,(285942,'FD WD PR 4970', '2016-07-10' ,533957,12154.25)
,(285893,'MAT WD PR 4963', '2016-07-15' ,NULL,176954.25)
,(285893,'MAT WD PR 4963', '2016-07-15',NULL,23045.75)

CREATE TABLE [Test].[dbo].[Withdrawal] (
[Date] datetime,
[Description] nvarchar(255),
[ChqNo] float,
[Amount] float
)

INSERT INTO [Test].[dbo].[Withdrawal]
([Date]
,[Description]
,[ChqNo]
,[Amount])
VALUES
('2016-04-01','Chq',250512,26822.67)
,('2016-05-01','Direct',NULL,900000)
,('2016-05-20','Chq',250523,47250)
,('2016-06-30','Other Chgs',NULL,5000)
,('2016-07-20','Direct',NULL,200000)

--My problem is to get;
--'PV number wise subtotal of the amount column of the 1st table (Pmt) with the matching details from the second table (Withdrawal) based on its amount column.If such sub total matches, then join such row with the 'Withdrawal' table & put it into a new table.

--Code for this i tried is
SELECT *
INTO [Test].[dbo].[expectedTable]
FROM [Test].[dbo].[pmt] p
inner join [Test].[dbo].[Withdrawal] b
WHERE (SELECT sum(p.Amount) as 'PmtAmount'
FROM [Test].[dbo].[pmt] p
GROUP BY p.PVNo) = b.[Amount];

-- Result i'm expecting is a table like this

image.png778×181 3.17 KB
PVNo Description Date ChqNo Amount W.Date W.Description W.ChqNo W.Amount
285922 ATL/16/154,151 PAYMENT 01-04-16 250512 10404.00 01-04-16 Chq 250512 26822.67
285922 ATL/16/154,151 PAYMENT 01-04-16 250512 16418.67 01-04-16 Chq 250512 26822.67
285904 PAYMENT FOR BRANCH RENT 01-05-16 900000.00 01-05-16 Direct 900000.00
285912 SE14110206 OD WAVE OFF 10-05-16 250523 47250.00 20-05-16 Chq 250523 47250
285944 100P1605 - se vq fd chq 2016-4-4 01-07-16 400000.00
285942 FD WD PR 4970 10-07-16 533957 12154.25
285893 MAT WD PR 4963 15-07-16 176954.25 20-07-16 Direct 200000
285893 MAT WD PR 4963 15-07-16 23045.75 20-07-16 Direct 200000

Pls. help me to get this done?

4 
DROP TABLE IF EXISTS Pmt;
GO

CREATE TABLE [dbo].[Pmt] (
[PVNo] float,
[Description] nvarchar(255),
[Date] datetime,
[ChqNo] float,
[Amount] float
)
GO

INSERT INTO [dbo].[pmt]
([PVNo]
,[Description]
,[Date]
,[ChqNo]
,[Amount])
VALUES
(285922,'ATL/16/154,151 PAYMENT FOR BRAN','2016-04-01',250512,10404)
,(285922,'ATL/16/154,151 PAYMENT FOR BRAN','2016-04-01',250512,16418.67)
,(285904,'PAYMENT FOR BRANCH RENT', '2016-05-01',NULL,900000)
,(285912,'SE14110206 OD WAVE OFF', '2016-05-10',250523,47250)
,(285944,'100P1605 - se vq fd chq', '2016-07-01' ,NULL,400000)
,(285942,'FD WD PR 4970', '2016-07-10' ,533957,12154.25)
,(285893,'MAT WD PR 4963', '2016-07-15' ,NULL,176954.25)
,(285893,'MAT WD PR 4963', '2016-07-15',NULL,23045.75);
GO

DROP TABLE IF EXISTS dbo.Withdrawal;
GO

CREATE TABLE [dbo].[Withdrawal] (
[Date] datetime,
[Description] nvarchar(255),
[ChqNo] float,
[Amount] float
)
GO

INSERT INTO [dbo].[Withdrawal]
([Date]
,[Description]
,[ChqNo]
,[Amount])
VALUES
('2016-04-01','Chq',250512,26822.67)
,('2016-05-01','Direct',NULL,900000)
,('2016-05-20','Chq',250523,47250)
,('2016-06-30','Other Chgs',NULL,5000)
,('2016-07-20','Direct',NULL,200000);
--My problem is to get;
--'PV number wise subtotal of the amount column of the 1st table (Pmt) with the matching details from the second table (Withdrawal) based on its amount column.If such sub total matches, then join such row of with the 'Withdrawal' table & put it into anew table.
--Code for this i tried is
SELECT p.PVNo
     , p.Description PmtDescr
     , p.Date PmtDate
     , p.ChqNo
     , p.Amount PmtAmt
     , b.Date WthdrlDate
     , b.Description WthdrlDescr
     , b.Amount WthdrlAmt
INTO [dbo].[expectedTable]
FROM [dbo].[pmt] p
LEFT JOIN [dbo].[Withdrawal] b  ON b.ChqNo = p.ChqNo
LEFT JOIN (SELECT p1.PVNo, Sum(p1.Amount) as PmtAmount
FROM [dbo].[pmt] p1
GROUP BY p1.PVNo) c ON c.PmtAmount  = b.[Amount]
AND c.PVNo = p.PVNo;
SELECT et.PVNo
     , et.PmtDescr
     , et.PmtDate
     , et.ChqNo
     , et.PmtAmt
     , et.WthdrlDate
     , et.WthdrlDescr
     , et.WthdrlAmt
FROM dbo.expectedTable et;
DROP TABLE dbo.pmt;
DROP TABLE dbo.expectedTable;
DROP TABLE dbo.Withdrawal;
GO

image.png830×200 8.11 KB

1 Like
5

Thank you very much,

How can i combine the records when the cheque number columns are blank.

I want to match the below records also.

778×61 1.23 KB
PVNo Description Date ChqNo Amount W.Date W.Description W.ChqNo W.Amount
285893 MAT WD PR 4963 15-07-16 176954.25 20-07-16 Direct 200000
285893 MAT WD PR 4963 15-07-16 23045.75 20-07-16 Direct 200000

Let say, we exclude all rows with the value in cheque number columns & combine the null value cheque number columns using the value match.

I sorry, i see that there is no other unique maching fields common to two data sets. What i can think of is the date column. I can say witdrwal table date (b.Date) is sholud not be a later date than 10 days from payment table date column (p.Date).

Thanks again for your time & efforts.