I am trying to identify an individual based on codes in our database. I need to assign a name to a person based on what the individuals codes are for that year. Donations.
I am trying to use an in statement in a case statement.. I can't seem to find any information on this being possible or not out there. It is not looking good though :(.
Escencially, what I need to find out is by grouping by donor and looking at all of the donors records. if they have given specific procedures..
Please see the code and I will be happy to explain in more detail what I am trying to do if you are confused..
select donor_id, donation_procedure,
WHEN donation_procedure IN ('PL', 'PE', 'PR', 'PP', 'RP','DR', 'LV', 'WB', 'HH', 'AU', 'CU', 'SM', 'Q0', 'TH' THEN 'REDCELL'
WHEN (donation_procedure IN ('PL', 'PE', 'PR', 'PP', 'PR')) AND (donation_procedure) NOT IN ('DR', 'LV', 'WB', 'HH', 'AU', 'CU', 'SM', 'Q0', 'TH')) THEN 'APHERESIS'
ELSE 'UNKNOWN' END
where (collection_date >= DATEADD(day, - 365, GETDATE()))
group by donor_id, donation_procedure
order by donor_id