@dblatt:
Your confidence in my memory is impressive. 
But I don’t. Have you guys dropped a list of ancient codes somewhere? @aostropolets needs you to fish them out of the old data, because the AMA or the CMS don’t carry old deprecated codes. They let them disappear. As horrible that is from an ontology management perspective, I have empathy with them because unless they totally annihilate the codes some dumb*** will keep using them.
Something like the following. Please run on your data:
-- Fishing HCPCS (1 letter, 4 digits):
select h.<code>, h.<description>, min(h.<date_stamp>) as start_date, max(h.<date_stamp>) as end_date
from (
select
t.<code>, -- the field containing the code
t.<description>, -- the field containing the description
t.<date_stamp>, -- the field containing the time stamp
substr(t.<code>, 1, 1) as l,
substr(t.<code>, 2, 1) as n1,
substr(t.<code>, 3, 1) as n2,
substr(t.<code,> 4, 1) as n3,
substr(t.<code>, 5, 1) as n4
from <your source table> as t-- where you are fishing, containing the fields code, description and date_stamp
left join concept as c on concept_code=t.<code>
where length(t.<code>)=5 -- is 5 letters long
and c.concept_id is null -- can't find it in the vocabulary
) as h
where h.l>='A' and h.l<='Z'
and h.n1>='0' and h.n1<='9'
and h.n2>='0' and h.n2<='9'
and h.n3>='0' and h.n3<='9'
and h.n4>='0' and h.n4<='9'
group by h.<code>, h.<description>
;
-- Fishing CPT-4 (3 digits and 2 alphanumerics)
select h.<code>, h.<description>, min(h.<date_stamp>) as start_date, max(h.<date_stamp>) as end_date
select
t.<code>, -- the field containing the code
t.<description>, -- the field containing the description
t.<date_stamp>, -- the field containing the time stamp
substr(t.<code>, 1, 1) as n1,
substr(t.<code>, 2, 1) as n2,
substr(t.<code>, 3, 1) as n3,
substr(t.<code>, 4, 1) as l1,
substr(t.<code>, 5, 1) as l2
from <your source table> as t-- where you are fishing, containing the fields code, description and date_stamp
left join concept as c on concept_code=t.<code>
where length(t.<code>)=5 -- is 5 letters long
and c.concept_id is null -- can't find it in the vocabulary
) as h
where h.n1>='0' and h.n1<='9'
and h.n2>='0' and h.n2<='9'
and h.n3>='0' and h.n3<='9'
and (h.l1>='A' and h.l1<='Z' or h.l1>='0' and h.l1<='9')
and (h.l2>='A' and h.l2<='Z' or h.l2>='0' and h.l2<='9')
group by h.<code>, h.<description>
;
