Conditional Distinct Count

[kdbenson]

I need to do a conditional count on a number of assessments. It needs to be a distinct count over person and date. My raw data looks something like this:

Name [tab]Date [tab]Assessment Type [tab]Assessment Time
Chuck Smith [tab]6/7/2010 [tab]1 [tab]6/7/10 1:00
Chuck Smith [tab]6/7/2010 [tab]2 [tab]6/7/10 1:00
Chuck Smith [tab]6/7/2010 [tab]3 [tab]6/7/10 1:00
Chuck Smith [tab]6/8/2010 [tab]1 [tab]6/8/10 1:00
Chuck Smith [tab]6/8/2010 [tab]1 [tab]6/8/10 2:00
Chuck Smith [tab]6/8/2010 [tab]1 [tab]6/8/10 3:00
Chuck Smith [tab]6/8/2010 [tab]2 [tab]6/8/10 1:00
Chuck Smith [tab]6/8/2010 [tab]2 [tab]6/8/10 2:00
Chuck Smith [tab]6/9/2010 [tab]2 [tab]6/9/10 3:00
Chuck Smith [tab]6/9/2010 [tab]3 [tab]6/9/10 14:00
Larry Jones [tab]6/7/2010 [tab]1 [tab]6/7/10 1:00
Larry Jones [tab]6/7/2010 [tab]2 [tab]6/7/10 1:00
Larry Jones [tab]6/8/2010 [tab]1 [tab]6/8/10 1:00
Larry Jones [tab]6/8/2010 [tab]1 [tab]6/8/10 2:00
Larry Jones [tab]6/8/2010 [tab]2 [tab]6/8/10 1:00
Larry Jones [tab]6/9/2010 [tab]1 [tab]6/9/10 1:00
Larry Jones [tab]6/9/2010 [tab]1 [tab]6/9/10 2:00
Larry Jones [tab]6/9/2010 [tab]2 [tab]6/9/10 1:00
Larry Jones [tab]6/9/2010 [tab]2 [tab]6/9/10 2:00
Larry Jones [tab]6/10/2010[tab]1 [tab]6/10/10 1:00
Larry Jones [tab]6/11/2010[tab]2 [tab]6/11/10 1:00

The result set should look something like the following:
Name [tab]Assessment Type 1 [tab]Assessment Type 2 [tab]Assessment Type 3
Chuck Smith [tab]2 [tab]3 [tab]2
Larry Jones [tab]4 [tab]4 [tab]0

If it didn't need to be a distinct count I could just use sum with a case statement, but this won't be distinct. I would like to use OLAP aggregates because I still need to bring back the detail data.

Thanks,
Kevin

 

[PHV]

Have you tried something like this ?

SELECT Name
, SUM(CASE AssessmentType WHEN 1 THEN 1 ELSE 0 END) AS Type1
, SUM(CASE AssessmentType WHEN 2 THEN 1 ELSE 0 END) AS Type2
, SUM(CASE AssessmentType WHEN 3 THEN 1 ELSE 0 END) AS Type3
FROM (SELECT DISTINCT Name, Date, AssessmentType FROM tblAssessments) D
GROUP BY Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kdbenson]

Thanks, PH. Unfortunately, this doesn't give me the detail data. I forgot to mention this caveat in the first post.

I had to do this in a couple of steps. First I create the unit by which I need to count. In this case it is a concatenation of Name, Assessment Date, and Assessment Type. Then I rank these values based on the assessment time. Then I can do a sum for each of the assessment types, but only of the records whose rank is 1. This is what makes the count distinct. The code looks like that below. It may not be exactly right because I was doing it on a different set of data with some different wrinkles, but the logic works. Note that I changed the column headings in the code to make it more coding friendly. This code returns all the detail data in addition to the aggregates.

-Kevin

SELECT
SUM(CASE WHEN Assessment_Type = 1 THEN 1 ELSE 0 END) OVER () AS Assessment_1_Count,
SUM(CASE WHEN Assessment_Type = 2 THEN 1 ELSE 0 END) OVER () AS Assessment_2_Count,
SUM(CASE WHEN Assessment_Type = 3 THEN 1 ELSE 0 END) OVER () AS Assessment_3_Count,
q1.name, q1.assessment_date, q1.assessment_type, q1.assessment_time
FROM (
SELECT
Name || CAST(assessment_date AS CHAR(10)) || CAST(assessment_type AS CHAR(2)) AS counting_unit,
RANK() OVER (PARTITION BY counting_unit ORDER BY assessment_time) AS assessment_rank,
table1.name, table1.assessment_date, table1.assessment_type, table1.assessment_time

FROM Table1
) q1