quote:

---

Originally posted by khovorka

I am submitting a query and getting the following results:

Female, 487
Male, 96

The results should be
Female, 4
Male, 1

I would appreciate any help in know what is out of sync here
or what I am missing so that the cnt value is expected.

Here is the query:

SELECT p1.sex, Count(rn) AS cnt FROM (
SELECT center, patientid, timestamp, date1, date2, date3, row_number()
OVER (partition by patientid ORDER BY timestamp DESC) AS rn
FROM dbo.tbl_mfc_goals) AS g
INNER JOIN dbo.tbl_mfc_pat1 AS p1 ON p1.patient = g.patientid
INNER JOIN dbo.tbl_current_status AS ps ON ps.lm_index = g.patientid
WHERE CONVERT(DATETIME, g.date1) > '2013-10-14' OR
CONVERT(DATETIME, g.date2) > '2013-10-14' OR
CONVERT(DATETIME, g.date3) > '2013-10-14'
AND g.rn = 1 AND g.center=51 AND p1.enrolldate <= '2013-10-14'
GROUP BY p1.sex

Thank you for your insight in getting me corrected on this issue.

Kim

Kim H.

---

SELECT  p1.sex ,
        COUNT(DISTINCT rn) AS cnt
....

--- and/or

....
                    date3 ,
                    ROW_NUMBER() OVER ( PARTITION BY center ORDER BY timestamp DESC ) AS rn
.....

SELECT p1.sex, Count(CASE THEN rn = 1 THEN 1 ELSE NULL END) AS cnt FROM...