September 19, 2002 at 9:41 am
SSN | ID | Status_ID |
-----------------------------------------
131764 | 1 | 120 |
131764 | 2 | 40 |
131764 | 3 | 50 |
131764 | 4 | 40 |
Above is a sample table. We need to do a Select statement that gets a count of unique
ssn which has a status_id of 50 and id is of greater value than a status_id of 40 for
the same ssn. As in the above example, this ssn would not count as 1, since the
status_id of 40 has an id of greater value than his status_id of 50.
September 19, 2002 at 10:56 am
Something like this should work....
select
count(distinct(a.SSN))
from SSCWork4 a
where
StatusID = 50
and ID > (
select max(b.ID)
from SSCWork4 b
where
StatusID = 40
and b.SSN = a.SSN)
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 19, 2002 at 11:02 am
You need to get the max ID for all the Status_ID = 40 rows. This requires a subquery.
Join that to back to the original table that has been parsed down to only where status_id = 50. Then you can do a comparison between the two result sets.
SELECT COUNT(DISTINCT A.SSN)
FROM MyTable A
JOIN (SELECT SSN, Status_ID, MAX(ID) ID
FROM MyTable
WHERE Status_ID = 40
GROUP BY SSN, Status_ID) B
ON A.SSN = B.SSN
WHERE A.Status_ID = 50
AND A.ID > B.ID
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply