Search code examples
sql-servert-sqlrollup

How to rollup duplicated records to specific record in query results

In my query results I'm getting duplicated vehicles for drivers in a specific policy and the results are fine in my initial query. Here are my results:

Rollup Question

From the pic above I would like to display the VEHICLE records for the POLICYHOLDER ONLY and remove those same vehicles from the ACTIVE records in the same policy. Here is what I was hoping it will look like:

Rollup Resolution

Here is my sql code:

WITH CTE1 as 
(
SELECT pr1.*
FROM #tempPIFRecords pr1
), 
CTE2 as (
SELECT pavr.*
FROM #tempPIFAssocVehicleRecords pavr
)

SELECT CTE1.*, CTE2.Year, CTE2.Make, CTE2.Model, CTE2.[Effective Date], CTE2.[Expiration Date]
FROM CTE1
LEFT OUTER JOIN CTE2
ON CTE1.AgentNo = CTE2.AgentNo
AND CTE1.PolicyNo = CTE2.PolicyNo
ORDER BY CTE1.AgentNo asc, CTE1.PolicyNo asc;

Any help/direction would be appreciated. Thank you.

Solution

  • This should be OK:

    WITH CTE1 as 
(
SELECT pr1.*
FROM #tempPIFRecords pr1
), 
CTE2 as (
SELECT pavr.*
FROM #tempPIFAssocVehicleRecords pavr
)

SELECT CTE1.*, ISNULL(CTE2.Year, '') AS Year, ISNULL(CTE2.Make, '') AS MAke, ISNULL(CTE2.Model, '') AS Model, ISNULL(CTE2.[Effective Date], '') AS [Effective Date], ISNULL(CTE2.[Expiration Date], '') AS [Expiration Date]
FROM CTE1
LEFT OUTER JOIN CTE2
ON CTE1.AgentNo = CTE2.AgentNo
AND CTE1.PolicyNo = CTE2.PolicyNo
AND CTE1.[DriverType] = 'PolicyHolder'
ORDER BY CTE1.AgentNo asc, CTE1.PolicyNo asc;