Two records into a single record?

Transact-SQL
1

Hope someone can help.

DECLARE @test AS TABLE
(
PolicyReference VARCHAR (30),
BusinessEventID VARCHAR (7),
PremValue DECIMAL (15, 2),
FeeValue DECIMAL (15, 2)
)
INSERT INTO @test
VALUES 
(
'123456', 'ABCDE10', '721.93', '0.00'
)
INSERT INTO @test
VALUES 
(
'123456', 'ABCDE10', '0.00', '25.00'
)

SELECT * FROM @test

Expected output:

123456	ABCDE10	721.93	25.00
2

Running your code, I get two records, which is to be expected since two INSERTs were done.

3

Depends on what your business logic is. If you want to one record for each PolicyReference and BusinessEventId, and if that record should show the TOTAL of PreValue and Fee Value, then you would query as:

SELECT
	PolicyReference,
	BusinessEventID,
	TotalPreValue = SUM(PremValue),
	TotalFeeValue = SUM(FeeValue)
FROM
	@test
GROUP BY
	PolicyReference,
	BusinessEventID;

But, if you were trying to get the MAX PreValue and MAX Fee value, you wouldn't use the SUM function, instead would use the MAX function.

1 Like
4

That works perfectly James, many thanks.