Unduplicated Counts by Program and Heirarchy?!

[nsalinas]

Hello all,
I am having trouble trying to figure out how to get unduplicated client counts by program and by heiarchy.

Clients should only be counted once -- and only in the first program in the heirarchy below to which the client belongs.

Example Program Heirarchy: Medicaid, Other Medicaid, Private Insurance, etc.

So, if a client has Medicaid AND Private Insurance listed as their programs... they should only be counted once in Medicaid, because it is first in the heirarchy.

I would appreciate any help... I have not been able to figure out how to even start...

Any help or direction would be greatly appreciated!
Thank you

 

[PDreyer]

Need more info
select count(distinct client) ClientCount
from
(select 1,'Med' union all
select 1,'Ins' union all
select 1,'OMed' union all
select 2,'Ins')
program(client,program)