สมัครสมาชิก

รายงาน Unit Cost

Administrator
โพสต์: 10
ลงทะเบียนเมื่อ: พุธ 09 ก.ค. 2014 10:41 am

รายงาน Unit Cost

โพสต์โดย admin » อังคาร 22 ก.ค. 2014 9:13 am

โดย pakee_06 » ศุกร์ 01 มิ.ย. 2012 8:09 am
โพสต์: 508
http://www.hospital-os.com/th/phpbb/viewtopic.php?f=5&t=3270

ผู้เริ่มต้น
ภาพประจำตัวสมาชิก
โพสต์: 9
ลงทะเบียนเมื่อ: อังคาร 15 ก.ค. 2014 10:53 pm

Re: รายงาน Unit Cost

โพสต์โดย chumsin » พฤหัสฯ. 24 ก.ค. 2014 5:41 pm

ipd.sql
โค้ด: เลือกทั้งหมด
select
        q.hcode
        ,q.AN
        ,q.HN
        ,q.VN
        ,q.pname
        ,q.fname
        ,q.lname
        ,q.birthday
        ,q.sex
        ,q.regdate
        ,q.dchdate
        ,q.wardname
        ,q.pttypename
        ,case when q.ICD10[1] is null then '' else q.ICD10[1] end  as pdx   
        ,case when q.ICD10[2] is null then '' else q.ICD10[2] end  as dx0   
        ,case when q.ICD10[3] is null then '' else q.ICD10[3] end  as dx1   
        ,case when q.ICD10[4] is null then '' else q.ICD10[4] end  as dx2   
        ,case when q.ICD10[5] is null then '' else q.ICD10[5] end  as dx3   
        ,case when q.ICD10[6] is null then '' else q.ICD10[6] end  as dx4   
        ,case when q.ICD10[7] is null then '' else q.ICD10[7] end  as dx5   
        ,case when q.ICD9[1] is null then '' else q.ICD9[1] end  as op0   
        ,case when q.ICD9[2] is null then '' else q.ICD9[2] end  as op1   
        ,case when q.ICD9[3] is null then '' else q.ICD9[3] end  as op2   
        ,case when q.ICD9[4] is null then '' else q.ICD9[4] end  as op3
        ,case when q.ICD9[5] is null then '' else q.ICD9[5] end  as op4   
        ,case when q.ICD9[6] is null then '' else q.ICD9[6] end  as op5
        ,case when q.ICD9[7] is null then '' else q.ICD9[7] end  as op6   

    ,q.group1 AS INC01
   ,q.group2 AS INC02
   ,q.group3 AS INC03
   ,q.group4 AS INC04
   ,q.group5 AS INC05
   ,q.group6 AS INC06
   ,q.group7 AS INC07
   ,q.group8 AS INC08
   ,q.group9 AS INC09
   ,q.group10 AS INC10
   ,q.group11 AS INC11
   ,q.group12 AS INC12
   ,q.group13 AS INC13
   ,q.group14 AS INC14
   ,q.group15 AS INC15
   ,q.group16 AS INC16
    ,q.group17 AS INC17
    ,q.los as los
    ,'' as drg
    ,'' as rw
    ,'' as adjrw

from

(select
        b_site.b_visit_office_id as hcode
        ,t_visit.visit_vn as AN
        ,t_patient.patient_hn as HN
        ,t_visit.visit_an as VN
        ,f_patient_prefix.patient_prefix_description as pname
        ,t_patient.patient_firstname as fname
        ,t_patient.patient_lastname as lname
        ,substring(t_patient.patient_birthday,6,2)||'/'||substring(t_patient.patient_birthday,6,2)||'/'||cast(substring(t_patient.patient_birthday,1,4)as numeric)-543 as birthday
        ,t_patient.f_sex_id as sex
        ,SUBSTRING(t_visit.visit_begin_visit_time,9,2)||'/'||SUBSTRING(t_visit.visit_begin_visit_time,6,2)||'/'||cast(SUBSTRING(t_visit.visit_begin_visit_time,1,4)as numeric)-543 as regdate
        ,SUBSTRING(t_visit.visit_staff_doctor_discharge_date_time,9,2)||'/'||SUBSTRING(t_visit.visit_staff_doctor_discharge_date_time,6,2)||'/'||cast(SUBSTRING(t_visit.visit_staff_doctor_discharge_date_time,1,4)as numeric)-543 as dchdate
        ,SUBSTRING(t_visit.visit_begin_admit_date_time,9,2)||'/'||SUBSTRING(t_visit.visit_begin_admit_date_time,6,2)||'/'||cast(SUBSTRING(t_visit.visit_begin_admit_date_time,1,4)as numeric)-543 as sleepdate
        ,b_visit_ward.visit_ward_description as wardname
        ,b_contract_plans.contract_plans_description as pttypename
         
        ,concat_icd10_2(t_visit.t_visit_id, '1')||concat_icd10_2(t_visit.t_visit_id, '2')||concat_icd10_2(t_visit.t_visit_id, '3')
                            ||concat_icd10_2(t_visit.t_visit_id, '4')||concat_icd10_2(t_visit.t_visit_id, '5') as ICD10
        ,concat_icd9_2(t_visit.t_visit_id, '1')||concat_icd9_2(t_visit.t_visit_id, '2')||concat_icd9_2(t_visit.t_visit_id, '3') as ICD9

    ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '1'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group1
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '2'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group2
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '3'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group3
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '4_OH'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group4
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '5'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group5
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '6'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group6
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '7'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group7
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '8'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group8
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '9'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group9
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'A'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group10
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'B'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group11
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'C'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group12
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'D'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group13
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'E'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group14
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'F'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group15
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'G'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group16
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'H'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group17
     ,case when (to_date(substring(visit_staff_doctor_discharge_date_time,1,10),'YYYY-MM-DD') -
    to_date(substring(t_visit.visit_begin_admit_date_time,1,10),'YYYY-MM-DD')) = 0
            then 1
            else (to_date(substring(visit_staff_doctor_discharge_date_time,1,10),'YYYY-MM-DD') -
    to_date(substring(t_visit.visit_begin_admit_date_time,1,10),'YYYY-MM-DD'))
    end as los
       
from
        t_visit inner join t_patient on t_patient.t_patient_id = t_visit.t_patient_id
        left join t_visit_payment on t_visit_payment.t_visit_id = t_visit.t_visit_id   and t_visit_payment.visit_payment_priority = '0' and t_visit_payment.visit_payment_active = '1'
        left join b_contract_plans on b_contract_plans.b_contract_plans_id = t_visit_payment.b_contract_plans_id
        left join f_patient_prefix on f_patient_prefix.f_patient_prefix_id = t_patient.f_patient_prefix_id
        inner join t_billing_invoice_item on t_billing_invoice_item.t_visit_id = t_visit.t_visit_id and t_billing_invoice_item.billing_invoice_item_active = '1'
        inner join b_item on b_item.b_item_id = t_billing_invoice_item.b_item_id
        left join b_item_16_group on b_item_16_group.b_item_16_group_id = b_item.b_item_16_group_id
        left join b_visit_ward on b_visit_ward.b_visit_ward_id = t_visit.b_visit_ward_id
        cross join b_site
where
        t_visit.f_visit_status_id = '3'
        and t_visit.f_visit_type_id ='1'
        and substr(t_visit.visit_begin_visit_time,1,10) between '2555-10-01' and '2556-09-30'
group by
        b_site.b_visit_office_id
        ,t_visit.visit_vn
        ,t_patient.patient_hn
        ,t_visit.visit_an
        ,t_patient.patient_pid
        ,f_patient_prefix.patient_prefix_description
        ,t_patient.patient_firstname
        ,t_patient.patient_lastname
        ,t_patient.patient_birthday
        ,t_patient.f_sex_id
        ,wardname
        ,t_visit.visit_begin_visit_time
        ,t_visit.visit_staff_doctor_discharge_date_time
        ,b_contract_plans.contract_plans_description
        ,ICD10
        ,ICD9
        ,sleepdate
,t_visit.f_visit_type_id
,t_visit.visit_financial_discharge_time
,visit_begin_admit_date_time
) as q

OPD
โค้ด: เลือกทั้งหมด
select
        q.hcode
        ,q.VN
        ,q.HN
        ,q.cid
        ,q.pname
        ,q.fname
        ,q.lname
        ,q.birthday
        ,q.sex
        ,q.vstdate
        ,q.pttypename
        ,case when q.ICD10[1] is null then '' else q.ICD10[1] end  as pdx   
        ,case when q.ICD10[2] is null then '' else q.ICD10[2] end  as dx0   
        ,case when q.ICD10[3] is null then '' else q.ICD10[3] end  as dx1   
        ,case when q.ICD10[4] is null then '' else q.ICD10[4] end  as dx2   
        ,case when q.ICD10[5] is null then '' else q.ICD10[5] end  as dx3   
        ,case when q.ICD10[6] is null then '' else q.ICD10[6] end  as dx4   
        ,case when q.ICD10[7] is null then '' else q.ICD10[7] end  as dx5   
        ,case when q.ICD9[1] is null then '' else q.ICD9[1] end  as op0   
        ,case when q.ICD9[2] is null then '' else q.ICD9[2] end  as op1   
        ,case when q.ICD9[3] is null then '' else q.ICD9[3] end  as op2   
        ,case when q.ICD9[4] is null then '' else q.ICD9[4] end  as op3
        ,case when q.ICD9[5] is null then '' else q.ICD9[5] end  as op4   
        ,case when q.ICD9[6] is null then '' else q.ICD9[6] end  as op5

    ,q.group1 AS INC01
   ,q.group2 AS INC02
   ,q.group3 AS INC03
   ,q.group4 AS INC04
   ,q.group5 AS INC05
   ,q.group6 AS INC06
   ,q.group7 AS INC07
   ,q.group8 AS INC08
   ,q.group9 AS INC09
   ,q.group10 AS INC10
   ,q.group11 AS INC11
   ,q.group12 AS INC12
   ,q.group13 AS INC13
   ,q.group14 AS INC14
   ,q.group15 AS INC15
   ,q.group16 AS INC16
    ,q.group17 AS INC17

from

(select
        b_site.b_visit_office_id as hcode
        ,t_visit.visit_vn as VN
        ,t_patient.patient_hn as HN
        ,t_patient.patient_pid as cid
        ,f_patient_prefix.patient_prefix_description as pname
        ,t_patient.patient_firstname as fname
        ,t_patient.patient_lastname as lname
        ,t_patient.patient_birthday as birthday
        ,t_patient.f_sex_id as sex
        ,Substring (t_visit.visit_begin_visit_time,1,10) as vstdate
        ,b_contract_plans.contract_plans_description as pttypename
         
        ,concat_icd10_2(t_visit.t_visit_id, '1')||concat_icd10_2(t_visit.t_visit_id, '2')||concat_icd10_2(t_visit.t_visit_id, '3')
                            ||concat_icd10_2(t_visit.t_visit_id, '4')||concat_icd10_2(t_visit.t_visit_id, '5') as ICD10
        ,concat_icd9_2(t_visit.t_visit_id, '1')||concat_icd9_2(t_visit.t_visit_id, '2')||concat_icd9_2(t_visit.t_visit_id, '3') as ICD9
,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '1'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group1
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '2'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group2
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '3'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group3
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '4_OH'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group4
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '5'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group5
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '6'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group6
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '7'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group7
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '8'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group8
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = '9'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group9
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'A'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group10
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'B'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group11
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'C'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group12
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'D'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group13
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'E'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group14
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'F'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group15
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'G'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group16
   ,SUM(CASE WHEN TRIM(b_item_16_group.item_16_group_number) = 'H'
         THEN t_billing_invoice_item.billing_invoice_item_total
         ELSE 0.00
   END) AS group17
       
from
        t_visit inner join t_patient on t_patient.t_patient_id = t_visit.t_patient_id
        left join t_visit_payment on t_visit_payment.t_visit_id = t_visit.t_visit_id   and t_visit_payment.visit_payment_priority = '0' and t_visit_payment.visit_payment_active = '1'
        left join b_contract_plans on b_contract_plans.b_contract_plans_id = t_visit_payment.b_contract_plans_id
        left join f_patient_prefix on f_patient_prefix.f_patient_prefix_id = t_patient.f_patient_prefix_id
        inner join t_billing_invoice_item on t_billing_invoice_item.t_visit_id = t_visit.t_visit_id and t_billing_invoice_item.billing_invoice_item_active = '1'
        inner join b_item on b_item.b_item_id = t_billing_invoice_item.b_item_id
        left join b_item_16_group on b_item_16_group.b_item_16_group_id = b_item.b_item_16_group_id
        cross join b_site
where
        t_visit.f_visit_status_id <> '4'
        and t_visit.f_visit_type_id ='0'
        and substr(t_visit.visit_begin_visit_time,1,10) between '2555-10-01' and '2556-09-30'
group by
        b_site.b_visit_office_id
        ,t_visit.visit_vn
        ,t_patient.patient_hn
        ,t_patient.patient_pid
        ,f_patient_prefix.patient_prefix_description
        ,t_patient.patient_firstname
        ,t_patient.patient_lastname
        ,t_patient.patient_birthday
        ,t_patient.f_sex_id
        ,t_visit.visit_begin_visit_time
        ,b_contract_plans.contract_plans_description
        ,ICD10
        ,ICD9
) as q

แนนเพิ่มเติมมาเรื่องสิทธิ์การรักษา
โค้ด: เลือกทั้งหมด
select contract_plans_number as HosRightID ,
contract_plans_description as HosRightName
 from b_contract_plans
where contract_plans_active ='1'

rawdata table of ipd
โค้ด: เลือกทั้งหมด
select q1."ChargeID" ,
q1."HN" ,
q1."AN" ,
case when q1."IPD" ilike 'W1' then 'D4001'
     when q1."IPD" ilike 'W2' then 'D4002'
     when q1."IPD" ilike 'LR' then 'D4003'
else '99999' end AS "CostCenterID" ,
q1."WorkDate" ,
q1."HosRightID" ,
q1."ItemID" ,
q1."Amount" ,
q1."Price" ,
q1."TotalPrice" 


FROM
(
select
case when t_order.b_item_16_group_id = '3120000000008' then   '01'
         when t_order.b_item_16_group_id = '3120000000011' then   '02'
         when t_order.b_item_16_group_id = '3120000000006' then   '03'
         when t_order.b_item_16_group_id = '3120000000004' then   '04'
         when t_order.b_item_16_group_id = '3120000000009' then   '05'
         when t_order.b_item_16_group_id = '3120000000012' then   '06'
         when t_order.b_item_16_group_id = '3120000000001' then   '07'
         when t_order.b_item_16_group_id = '3120000000002' then   '08'
         when t_order.b_item_16_group_id = '3120000000005' then   '09'
         when t_order.b_item_16_group_id = '3120000000013' then   '10'
         when t_order.b_item_16_group_id = '312112187704078725' then   '11'
         when t_order.b_item_16_group_id = '312112185381527091' then   '12'
         when t_order.b_item_16_group_id = '3120000000014' then   '13'
         when t_order.b_item_16_group_id = '312112181090897152' then   '14'
         when t_order.b_item_16_group_id = '312112181609368950' then   '15'
         when t_order.b_item_16_group_id = '3120000000010' then   '16'
ELSE t_order.b_item_16_group_id end as "ChargeID" ,
t_visit.visit_hn as "HN" ,
t_visit.visit_vn as "AN" ,
--t_order.b_item_16_group_id , --ตรวจสอบ

substring(t_order.order_date_time,9,2)||'/'||substring(t_order.order_date_time,6,2)||'/'||substring(t_order.order_date_time,1,4)as "WorkDate" ,
b_contract_plans.contract_plans_number as "HosRightID" ,
b_item.item_number as "ItemID" ,
t_order.order_qty as "Amount" ,
t_order.order_price as "Price" ,
t_order.order_qty *  t_order.order_price as "TotalPrice" ,
t_order.order_common_name as "รายการ"   ,--ตรวจสอบ
case    when t_visit.b_visit_ward_id ILIKE '2231139210212' then 'W1'
         when t_visit.b_visit_ward_id ILIKE '262112181488673297' then 'W2'
         when t_visit.b_visit_ward_id ILIKE '262112186056163808' then 'LR'
         else 'OPD' end AS "IPD"
--*
from t_order
INNER JOIN b_item on b_item.b_item_id = t_order.b_item_id -- 115449
INNER JOIN t_visit on  t_visit.t_visit_id = t_order.t_visit_id --  103196 เรคคอรด
INNER JOIN t_visit_payment on t_visit.t_visit_id = t_visit_payment.t_visit_id -- 115449
INNER JOIN b_contract_plans on t_visit_payment.b_contract_plans_id = b_contract_plans.b_contract_plans_id --115449
where substring(t_order.order_date_time,1,10) BETWEEN '2555-10-01' and '2556-09-30'
and t_order.f_order_status_id <> '3'
and t_visit.f_visit_status_id <> '4' -- -การรับบริการไม่ยกเลิก 115424  record
and t_visit.f_visit_type_id ='1' --ผู้ป่วยใน

ORDER BY "รายการ"
) as q1

rawdata table of opd
โค้ด: เลือกทั้งหมด
select q1."ChargeID" ,
q1."HN" ,
q1."VN" ,
--case when q1."IPD" ilike 'W1' then 'D4001'
--     when q1."IPD" ilike 'W2' then 'D4002'
--     when q1."IPD" ilike 'LR' then 'D4003'
--else '99999' end AS "CostCenterID" , --ใช้เฉพาะผู้ป่วยใน
q1."CostCenterID" ,

--q1."OPD" ,
q1."WorkDate" ,
q1."HosRightID" ,
q1."ItemID" ,
q1."Amount" ,
q1."Price" ,
q1."TotalPrice" 


FROM
(
select
case when t_order.b_item_16_group_id = '3120000000008' then   '01'
         when t_order.b_item_16_group_id = '3120000000011' then   '02'
         when t_order.b_item_16_group_id = '3120000000006' then   '03'
         when t_order.b_item_16_group_id = '3120000000004' then   '04'
         when t_order.b_item_16_group_id = '3120000000009' then   '05'
         when t_order.b_item_16_group_id = '3120000000012' then   '06'
         when t_order.b_item_16_group_id = '3120000000001' then   '07'
         when t_order.b_item_16_group_id = '3120000000002' then   '08'
         when t_order.b_item_16_group_id = '3120000000005' then   '09'
         when t_order.b_item_16_group_id = '3120000000013' then   '10'
         when t_order.b_item_16_group_id = '312112187704078725' then   '11'
         when t_order.b_item_16_group_id = '312112185381527091' then   '12'
         when t_order.b_item_16_group_id = '3120000000014' then   '13'
         when t_order.b_item_16_group_id = '312112181090897152' then   '14'
         when t_order.b_item_16_group_id = '312112181609368950' then   '15'
         when t_order.b_item_16_group_id = '3120000000010' then   '16'
ELSE t_order.b_item_16_group_id end as "ChargeID" ,
t_visit.visit_hn as "HN" ,
t_visit.visit_vn as "VN" ,
--t_order.b_item_16_group_id , --ตรวจสอบ
case    when b_service_point.service_point_description ilike  '01.ห้องเวชระเบียน' then 'C5001'
   when b_service_point.service_point_description ilike  '02.คัดกรอง OPD' then 'C5001'
   when b_service_point.service_point_description ilike  '03.หน้าห้องตรวจ' then 'C5001'
   when b_service_point.service_point_description ilike  '04.คำแนะนำหลังตรวจ/นัด' then 'C5001'
   when b_service_point.service_point_description ilike  '05.ห้องตรวจ OPD' then 'C5001'
   when b_service_point.service_point_description ilike  '06.คัดกรอง ER' then 'C6001'
   when b_service_point.service_point_description ilike  '07.ห้องฉุกเฉิน' then 'C6001'
   when b_service_point.service_point_description ilike  '08.พยาบาล ER' then 'C6001'
   when b_service_point.service_point_description ilike  '09.คัดกรองทันตกรรม' then 'C6101'
   when b_service_point.service_point_description ilike  '08.ห้องทันตกรรม' then 'C6101'
   when b_service_point.service_point_description ilike  '11.จุดคัดกรองห้องคลอด (LR)' then 'D4003'
   when b_service_point.service_point_description ilike  '12.COPD Clinic' then 'C5101'
   when b_service_point.service_point_description ilike  '13.ห้องผ่าตัด OR' then 'C1101'
   when b_service_point.service_point_description ilike  '12.แพทย์แผนไทย' then 'C6201'
   when b_service_point.service_point_description ilike  '23.งานกายภาพบำบัด' then 'C0901'
   when b_service_point.service_point_description ilike  '16.จุดคัดกรองคลินิคพิเศษ' then 'C5101'
   when b_service_point.service_point_description ilike  '17.ห้องตรวจคลินิคพิเศษ' then 'C5101'
   when b_service_point.service_point_description ilike  'คลินิก..ANC' then 'D4003'
   when b_service_point.service_point_description ilike  '19.ห้องตรวจ ANC (แม่และเด็ก) (เก่า)' then 'D4003'
   when b_service_point.service_point_description ilike  '19.ห้องตรวจ ANC (แม่และเด็ก) ใหม่' then 'D4003'
   when b_service_point.service_point_description ilike  '20.คลินิควัณโรค' then 'C5101'
   when b_service_point.service_point_description ilike  '21.ห้องให้คำปรึกษา' then 'C5101'
   when b_service_point.service_point_description ilike  '22.ห้องยา' then 'C5001'
   when b_service_point.service_point_description ilike  '23.การเงิน' then 'C5001'
   when b_service_point.service_point_description ilike  '09.ห้องชันสูตรโรค' then 'C5001'
   when b_service_point.service_point_description ilike  '25.งานประกัน' then 'C5001'
   when b_service_point.service_point_description ilike  '26.Ward1(หอผู้ป่วยในชาย)' then 'D4001'
   when b_service_point.service_point_description ilike  '27.Ward2(หอผู้ป่วยในหญิง)' then 'D4002'
   when b_service_point.service_point_description ilike  '28.Ward2(หอผู้ป่วยในหญิง)' then 'C0101'
   when b_service_point.service_point_description ilike  '10.ห้องรังสีวิทยา' then 'C0301'
   when b_service_point.service_point_description ilike  '30.คลินิก  DM' then 'C5101'
   when b_service_point.service_point_description ilike  '31.ตรวจสอบการแพ้ยา%' then 'C5001'
   when b_service_point.service_point_description ilike  '32.คลินิก HT' then 'C5101'
   when b_service_point.service_point_description ilike  '33.คลีนิคโรคตา' then 'C5101'
   when b_service_point.service_point_description ilike  '34.การเงินทันตกรรม' then 'C6101'
   when b_service_point.service_point_description ilike  '11.PCU' then 'E0501'
   when b_service_point.service_point_description ilike  '36.ฉีดยาทำแผล' then 'C6001'
   when b_service_point.service_point_description ilike  '37.ASTHMA' then 'C5101'
   when b_service_point.service_point_description ilike  '38.งานอนามัยโรงเรียน' then 'E0501'
   when b_service_point.service_point_description ilike  '17.งานบริการส่งเสริมสุขภาพ' then 'E0501'
   when b_service_point.service_point_description ilike  '41.คลีนิค wafarin' then 'C5101'
   when b_service_point.service_point_description ilike  '42.คลินิกนรีเวช' then 'D4003'
   when b_service_point.service_point_description ilike  '09.ห้องชันสูตรโรค' then 'D4003'
   when b_service_point.service_point_description ilike  '44.คลินิกอายุรกรรม' then 'C5001'
   when b_service_point.service_point_description ilike  '46.คลินิกพัฒนาการเด็ก' then 'D4003'
   when b_service_point.service_point_description ilike  'คลีนิคกุมารเวช' then 'D4003'
   when b_service_point.service_point_description ilike  'งานส่งเสริมสุขภาพ-สุขาภิบาล' then 'E0501'
else 'C5001' end as "CostCenterID"  , --เฉพาะผู้ป่วยนอก

substring(t_order.order_date_time,9,2)||'/'||substring(t_order.order_date_time,6,2)||'/'||substring(t_order.order_date_time,1,4)as "WorkDate" ,
b_contract_plans.contract_plans_number as "HosRightID" ,
b_item.item_number as "ItemID" ,
t_order.order_qty as "Amount" ,
t_order.order_price as "Price" ,
t_order.order_qty *  t_order.order_price as "TotalPrice" ,
t_order.order_common_name as "รายการ"  --ตรวจสอบ
--case    when t_visit.b_visit_ward_id ILIKE '2231139210212' then 'W1'
--         when t_visit.b_visit_ward_id ILIKE '262112181488673297' then 'W2'
--         when t_visit.b_visit_ward_id ILIKE '262112186056163808' then 'LR'
--         else 'OPD' end AS "IPD" --ใช้เฉพาะผู้ป่วยใน
--


from t_order
INNER JOIN b_item on b_item.b_item_id = t_order.b_item_id -- 115449
INNER JOIN t_visit on  t_visit.t_visit_id = t_order.t_visit_id --  103196 เรคคอรด
INNER JOIN t_visit_payment on t_visit.t_visit_id = t_visit_payment.t_visit_id -- 115449
INNER JOIN b_contract_plans on t_visit_payment.b_contract_plans_id = b_contract_plans.b_contract_plans_id --115449
INNER JOIN b_service_point ON b_service_point.b_service_point_id = t_order.order_service_point  --ใช้เฉพาะผู้ป่วยนอก


where substring(t_order.order_date_time,1,10) BETWEEN '2555-10-01' and '2556-09-30'
and t_order.f_order_status_id <> '3'
and t_visit.f_visit_status_id <> '4' -- -การรับบริการไม่ยกเลิก 115424  record
and t_visit.f_visit_type_id ='0' --ผู้ป่วยนอก


ORDER BY "รายการ"
) as q1
Mr. Chumsin Sriyan thapae Hospital Satun
Nhso zone 12
Server-IBM.Ram4G
OS -UBUNTU 11.04 64-bit
Hospital-OS Version. -Hos3.9.33bulidxx
Postgresql-8.4.x
Backup HP ram2G -Slony-I

Administrator
โพสต์: 10
ลงทะเบียนเมื่อ: พุธ 09 ก.ค. 2014 10:41 am

Re: รายงาน Unit Cost

โพสต์โดย admin » พฤหัสฯ. 24 ก.ค. 2014 5:46 pm

จัดเต็มมาเลย

ย้อนกลับไปยัง รายงาน Hospital OS

ผู้ใช้งานขณะนี้

กำลังดูบอร์ดนี้: ไม่มีสมาชิกใหม่ และ บุคคลทั่วไป 1 ท่าน

Fatal: Not able to open ./cache/data_global.php