Oncology_Regimen_Utilization

select r.regimen_name
    , count = count(ra.regimen_action_id)
from regimen r
    , regimen_action ra
    , person p
    , prsnl pr
    , encounter e
plan r where r.order_dt_tm > cnvtdatetime(cnvtdate(022417),0)
join ra where ra.regimen_id = r.regimen_id
    and ra.action_type_cd = value(uar_get_code_by("MEANING", 4002500, "ORDER"))
join e where r.encntr_id = e.encntr_id
    and e.active_ind = 1
    and e.end_effective_dt_tm > sysdate
join p where p.person_id = e.person_id
    and p.active_ind = 1
    and p.end_effective_dt_tm > sysdate
    and p.name_last_key not like '*TEST*'
join pr where pr.person_id = ra.action_prsnl_id
group by r.regimen_name
order by r.regimen_name
with format(date,"mm/dd/yyyy hh:mm:ss")