Using MIN() -- unable to figure how and where to structure a subquery properly
Posted on 2011-09-09
A common issue I'm having is how to use the MIN() function properly in a subquery so that I only return ONE record per row/ID.
I'm trying to find the earliest claim date that a member may have been diagnosed with COPD or emphysema during a specified time period and currently have:
SELECT DISTINCT t.n, c.CLAIM_NUM, c.ICD1, MIN(c.DOS)as MinDxDate, c.ICD2, c.ICD3, c.ICD4
-- n is a unique member id
FROM #temp_pcp t --temp table that has member and their respective PCPs in it
inner join dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID
WHERE (c.ICD1 IN('491','492', '496')
or c.ICD2 IN('491','492', '496')
or c.ICD3 IN('491','492', '496')
or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
GROUP BY t.n, DOS, CLAIM_NUM, c.ICD1, c.ICD2, c.ICD3, c.ICD4
ORDER BY t.N
It seem there has to be a subquery along the lines of (SELECT MIN(c.DOS) FROM claims); however, I cannot seem to fit it around the WHERE clause without causing errors and/or logic issues.
Any help much appreciated!!!