این گزارش بیشترین نحوه مصرفی، بیشترین تعداد تجویز و بیشترین توضیحاتی که پزشکان برای تجویز استفاده کرده اند را در 3 ماه گذشته استخراج می کند.

برای استخراج این گزارش ابتدا با استفاده از 3 جدول پایه تولید شد

جدول پایه برای استخراج نحوه مصرف:

create table sharifi.rep_serviceconsumptionV1 as
select p.cscode as drug,consumption || '_' || numberofperiod as consumption,count(1) as cnt
from thdksubscription hdk,partnercare.tpncproduct p
where 1=1
    and hdk.service_id=p.id
    and hdk.CREATIONDATE>(select pkgiutil.fncdatetoepoch(sysdate-90) from dual)
group by p.cscode,consumption || '_' || numberofperiod;

جدول پایه برای استخراج توضیحات:

create table sharifi.rep_serviceconsumptionV2 as
select p.cscode as drug,hdk.description,count(1) as cnt
from thdksubscription hdk,partnercare.tpncproduct p
where 1=1
    and hdk.service_id=p.id
    and hdk.CREATIONDATE>(select pkgiutil.fncdatetoepoch(sysdate-90) from dual)
    and hdk.description is not null
group by p.cscode,hdk.description;

create table sharifi.rep_serviceconsumptionV22 as
select p.cscode as drug,hdk.description,count(1) as cnt
from thdksubscription hdk,partnercare.tpncproduct p
where 1=1
    and hdk.service_id=p.id
    and hdk.CREATIONDATE>(select pkgiutil.fncdatetoepoch(sysdate-90) from dual)
group by p.cscode,hdk.description;

جدول پایه برای استخراج تعداد تجویز:

create table sharifi.rep_serviceconsumptionV3 as
select p.cscode as drug,hdk.numberofrequest,count(1) as cnt
from thdksubscription hdk,partnercare.tpncproduct p
where 1=1
    and hdk.service_id=p.id
    and hdk.CREATIONDATE>(select pkgiutil.fncdatetoepoch(sysdate-90) from dual)
group by p.cscode,hdk.numberofrequest;

سپس با استفاده از کوئری زیر داده های نهایی استخراج شد. در حقیقت گزارش زیر بیشترین مقدار را برای یک دارو انتخاب می کند.

select 
        to_char(t1.drug) as genericcode,
        '-' || (case when length(t1.drug)<=5 then lpad(t1.drug,5,'0')
          when length(t1.drug)<=16 then lpad(t1.drug,16,'0')
        else to_char(t1.drug) end) as nationalnumber
        ,(select name from partnercare.tpncproduct where nationalnumber=-t1.drug and dto is null and rownum=1) as productname
        ,(case substr(consumption,1,1) 
            when 'X' then 'Every two hours' 
            when 'A' then 'Every three hours' 
            when 'B' then 'Every four hours' 
            when 'C' then 'Every six hours' 
            when 'D' then 'Every eight hours' 
            when 'E' then 'Every twelve hours' 
            when 'b' then 'Every thirty six hours' 
            when 'F' then 'Once a day' 
            when 'G' then 'Every other day' 
            when 'Q' then 'Every other night' 
            when 'K' then 'Twice a day'   
            when 'I' then 'Three times a day'
            when 'J' then 'Four times a day'
            when 'Y' then 'Every ten days'
            when 'c' then 'Every twenty eight days'
            when 'L' then 'Every hour'
            when 'H' then 'Every week'
            when 'M' then 'Every month'
            when 'N' then 'Every year'
            when 'P' then 'Every morning'
            when 'O' then 'Every night'
            when 'R' then 'Every other week'
            when 'S' then 'Every three weeks'
            when 'T' then 'Twice a week'
            when 'U' then 'Every three months'
            when 'V' then 'Every six months'
            when 'W' then 'Five days a week'
            when 'i' then 'As directed'
            when 'j' then 'As needed'
            else substr(consumption,1,1) 
        end) as consumption
        ,(case substr(consumption,2,1) 
            when 'M' then '0.5 ml' 
            when 'E' then '1 ml' 
            when 'F' then '1.5 ml' 
            when 'G' then '2 ml' 
            when 'A' then '2.5 ml' 
            when 'H' then '3 ml' 
            when 'I' then '3.5 ml' 
            when 'J' then '4 ml' 
            when 'B' then '5 ml' 
            when 'K' then '7.5 ml' 
            when 'C' then '10 ml' 
            when 'D' then '15 ml' 
            when 'L' then '30 ml' 
            else substr(consumption,2,1)
        end) as ConsumptionInstruction
        ,numberofperiod
        ,t1.HIGHEST as Percentofconsumption
        ,numberofrequest as count
        ,t2.HIGHEST as Percentofnumberofrequest
        ,description
        ,t3.HIGHEST as Percentofdescription
        ,t3.AllHIGHEST as PercentofAlldescription
from 
(select 
    drug
    ,substr(consumption,1,instr(consumption,'_')-1) as consumption
    ,substr(consumption,instr(consumption,'_')+1) as numberofperiod
    ,round(HIGHEST* 100 /(select sum(cnt) from sharifi.rep_serviceconsumptionV1 where drug=s.drug)) as HIGHEST
from
(
    select drug,(select consumption from sharifi.rep_serviceconsumptionV1 where cnt=HIGHEST and drug=v.drug and rownum=1) as consumption,HIGHEST from(
        SELECT DISTINCT drug, FIRST_VALUE(cnt) 
         OVER (PARTITION BY drug ORDER BY cnt desc
               RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
               AS HIGHEST
        FROM sharifi.rep_serviceconsumptionV1 v
    )v
)s) t1 join 
(select drug,(select numberofrequest from sharifi.rep_serviceconsumptionV3 where cnt=HIGHEST and drug=v.drug and rownum=1) as numberofrequest
    ,round(HIGHEST* 100 /(select sum(cnt) from sharifi.rep_serviceconsumptionV3 where drug=v.drug)) as HIGHEST from(
    SELECT DISTINCT drug, FIRST_VALUE(cnt) 
     OVER (PARTITION BY drug ORDER BY cnt desc
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
           AS HIGHEST
    FROM sharifi.rep_serviceconsumptionV3 v
)v) t2 on t1.drug= t2.drug
left join 
(select drug,(select description from sharifi.rep_serviceconsumptionV2 where cnt=HIGHEST and drug=v.drug and rownum=1) as description
    ,round(HIGHEST* 100 /(select sum(cnt) from sharifi.rep_serviceconsumptionV2 where drug=v.drug)) as HIGHEST
    ,round(HIGHEST* 100 /(select sum(cnt) from sharifi.rep_serviceconsumptionV22 where drug=v.drug)) as AllHIGHEST
    from(
    SELECT DISTINCT drug, FIRST_VALUE(cnt) 
     OVER (PARTITION BY drug ORDER BY cnt desc
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
           AS HIGHEST
    FROM sharifi.rep_serviceconsumptionV2 v
)v)t3 on t2.drug=t3.drug