Help trying to match payments to invoices

Transact-SQL
1 
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.

2

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
3

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