Hello everybody,
I am using the same field three times over in a case statement which I think is leading to duplication in my results set. The end user has stated they want the three columns in the results set. I get even more duplicates if I don't use the Max function. This is the query:
SELECT
pt.case_detail AS [Quote Ref],
sd.section AS [Team Number]
ph.pol_id AS [Policy ID],
pst.description AS [Policy Description],
MAX (cr.date_in) AS [Date Received],
ph.additional_info AS [Reference Field 1],
t.description AS [Task Description],
CASE
WHEN MAX(ad.FieldId) = 67 THEN ad.FieldValue ELSE NULL
END AS [Requestor Auth'd],
CASE
WHEN MAX(ad.FieldId) = 68 THEN ad.FieldValue ELSE NULL
END AS [Ben Statement],
CASE
WHEN MAX(ad.FieldId) = 69 THEN ad.FieldValue ELSE NULL
END AS [Paid Up]
FROM account_data ad
INNER JOIN policy_header ph
ON ad.ProcessInstanceId = ph.process_instance
INNER JOIN policy_detail_header pdh
ON pdh.pol_id = ph.pol_id
INNER JOIN policy_status_types pst
ON ph.policy_status = pst.policy_status
INNER JOIN sales_detail sd
ON pdh.section = sd.section
INNER JOIN policy_tasks pt
ON pt.process_instance = ph.process_instance
INNER JOIN tasks t
ON t.section = pt.section
AND t.task_number = pt.task_number
INNER JOIN campaign_records cr
ON cr.case_detail = pt.case_detail
INNER JOIN efinance ef
ON ad.FieldId = ef.Id
AND ad.FormId = ef.FormId
WHERE t.task_number IN (47, 48)
GROUP BY
pt.case_detail,
sd.section,
ph.pol_id,
pst.description,
ph.additional_info,
t.description
The [Quote Ref] should be the unique identifier. Query works as intended with one case statement but duplicates come in thereafter. I am fairly inexperienced with SQL.
Is there another way of writing this query that will remove the duplicates? (Distinct made no difference when I tried it)
Thanks for your help