SQL Heirachy Counts help

[mikeyb10supra]

Hey guys, im running some counts in SQL and want them to be run as a heirachy, ie if they fall into the first case ie pledger, then they wont appear in the next two case statements. Im pretty sure what I have done below correct and will it work as I have said. Be useful to get a second opinion

 

 

 

 

SELECT LEGACY_STATUS, count(distinct contact_number) FROM

 

(select contact_number,

CASE WHEN activity_value in ('PLE', 'PEC', 'RES', 'FWS') THEN 'LEG_PLEDGERS'

WHEN activity_value = 'INT' THEN 'LEG_INTENDERS'

WHEN activity_value in ('ENQ','BKLT','FWLT') THEN 'LEG_ENQUIRER' ELSE 'NO_LEGACY' END AS LEGACY_STATUS

FROM contact_categories

where activity = 'LEG')

 

GROUP BY LEGACY_STATUS;

[MrRalphMan]

I've done something very similar at work, which unfortunately I am not back in until Sunday.

 

Basicly you have the same type of nested query, but the nested query returns either a 1 or 0 for the results and you just SUM the results in the top level query.

 

This link has something similar in it, once I'm back in work I can pass you what I have done.

 

Cheers,

 

Paul.

[mikeyb10supra]

I've done something very similar at work, which unfortunately I am not back in until Sunday.

 

Basicly you have the same type of nested query, but the nested query returns either a 1 or 0 for the results and you just SUM the results in the top level query.

 

This link has something similar in it, once I'm back in work I can pass you what I have done.

 

Cheers,

 

Paul.

 

 

Will that take out dupes though?? If condition 1 is satisfied and they have a pledger activity then its = 1

 

But if condition 2 is also satisfied and the same person picked up above also has an 'INT' value surely they will be counted again?