SQLTeam.com | Weblogs | Forums

Help trying to match payments to invoices

DECLARE @test AS TABLE
(
TDate VARCHAR (8),
NomCode VARCHAR (7),
HeaderRef VARCHAR (20),
Value DECIMAL (15, 2),
TranType VARCHAR (3)
)
INSERT INTO @test
VALUES 
(
'20160301', 'IN10025', '716656', '1900.00', 'INV'
)
INSERT INTO @test
VALUES 
(
'20160501', 'IN10025', '716656', '450.00', 'PAY'
)
INSERT INTO @test
VALUES 
(
'20160616', 'IN10025', '716656', '450.00', 'PAY'
)
INSERT INTO @test
VALUES 
(
'20160716', 'IN10025', '716656', '1000.00', 'PAY'
)

SELECT * FROM @test

Hope someone can help.

What I'm trying to do is match the three payments against the one invoice; the header ref field is the invoice number which is easy enough but what I need to do is capture the invoice date not the payment date.

So in the example above, I need to capture 1st March 2016.

I've tried using a couple of CTE's (one for invoices, one for payments) but I'm struggling to work out how to bring through the invoice date.

Any help appreciated as always.

What is the output you are looking for?

Something along these lines?

;WITH Invoices AS
(
	SELECT	TDate, NomCode, SUM(Value) AS Invoices
	FROM @test WHERE TranType = 'INV'
	GROUP BY TDate, NomCode
),
Payments AS 
(
	SELECT	TDate, NomCode, HeaderRef, SUM(Value) Payments
	FROM @test WHERE TranType = 'PAY'
	GROUP BY TDate, NomCode, HeaderRef
)
SELECT
	i.*,p.*
FROM
	Invoices i
	INNER JOIN Payments p ON
		p.NomCode = i.NomCode
		-- and ???
2 Likes

Thanks for that. Let me have a think about what my ideal output is - your code gets me close.