科室医生-SQL

--科室病种医生
with patientCase as (
    select pc.visit_id,
           pc.id,
           pc.disease_code              AS BINGZHONG,
           pc.report_status,
           v.out_time,
           df.title,
           doc.work_no,
           doc.name,
           v.source_doctor_id            AS YISHENG,
           v.source_dept_id,
           d.name                         AS KESHI,
           pc.report_time
    from mqm.patient_case pc
              left join  mqm.visit v on v.id = pc.visit_id
              left join mqm.disease_form df on pc.disease_code = df.code
              left join  mqm.department d on v.source_dept_id = d.source_dept_id
              left join mqm.doctor doc on v.source_doctor_id = doc.work_no
    where
             (d.source_dept_id is not null and  v.source_doctor_id is not null )
      --and  v.source_doctor_id is  null

             AND V.source_doctor_id = doc.work_no

          -- and
            -- v.source_doctor_id = '0546'--医生工号
       and
               v.source_dept_id = 221
    --   AND        v.source_doctor_id is null
       --and pc.report_status !='9'

     and ((v.out_time :: date between '2000-01-01' and current_date)  or  v.reg_time :: date between '2000-01-01' and current_date)

),
     disease_name as (
         select    count(p.id),
                     P.BINGZHONG,
                     p.title,
                     p.work_no,
                    p.source_dept_id
               from patientCase p
         group by P.BINGZHONG, p.title,p.work_no,p.source_dept_id),

    yisheng   as (
     select   count(p.id),
                  P.YISHENG               as 医生ID,
                  p.work_no               as 工号,
                  p.name                  as 医生,
                  p.BINGZHONG
     from patientCase p
         group by P.BINGZHONG, P.YISHENG, p.work_no, p.name),

     keshi   as (
     select   count( p.id),
                p.work_no,
                P.source_dept_id         AS  科室ID,
                P.BINGZHONG,
                P.KESHI                  AS 科室
         FROM patientCase p
         group by p.work_no, P.source_dept_id,P.BINGZHONG, P.KESHI ),

     --应上报人数
     yingshangbao as (
         select P.BINGZHONG, count(p.id) as 医生应上报人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where   p.report_status != '9'
         group by P.BINGZHONG, p.work_no, p.source_dept_id
     ),
     --待上报人数
     daishangbao as (
         select P.BINGZHONG, count(p.id) as 医生待上报人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (0, 5, 10)
         group by P.BINGZHONG,p.work_no,p.source_dept_id
     ),
     --进入排除审批流程人数
     paichushenpi as (
         select P.BINGZHONG, count(p.id) as 医生进入排除审批流程人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (11, 12)
         group by P.BINGZHONG,p.work_no,p.source_dept_id
     ),
     --已排除人数
     yipaichu as (
         select  P.BINGZHONG, count(p.id) as 医生已排除人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (9)
         group by P.BINGZHONG, p.work_no,p.source_dept_id
     ),
     --进入申请修改审批流程人数
     shenqingxiugai as (
         select P.BINGZHONG, count( p.id) as 医生进入申请修改审批流程人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (25)
         group by P.BINGZHONG, p.work_no,p.source_dept_id
     ),
     --填报端退回确认人数
     tuihuiqueren as (
         select P.BINGZHONG, count( p.id) as 医生填报端退回确认人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (90)
         group by P.BINGZHONG, p.work_no,p.source_dept_id
     ),
     --上报完成人数
     shangbaowancheng as (
         select P.BINGZHONG, count( p.id) as 医生上报完成人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (100)
         group by P.BINGZHONG, p.work_no,p.source_dept_id
     ),
     --已上报人数
     yishangbao as (
         select P.BINGZHONG, count( p.id) as 医生已上报人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status not in (0, 5, 9, 10)
         group by P.BINGZHONG, p.work_no,p.source_dept_id
     ),
     --患者出院10日内已上报人数
     huanzhe10shangbao as (
         select P.BINGZHONG, count(distinct p.id) as 患者出院10日内已上报人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status not in (0, 5, 10)
           and p.out_time + '10 days' >=   p.report_time
         group by P.BINGZHONG, p.work_no,p.source_dept_id
     ),
     --患者出院10日内未上报人数
     huanzhe10weishangbao as (
         select P.BINGZHONG, count(distinct p.id) as 患者出院10日内未上报人数,
                p.work_no,
                p.source_dept_id
         from patientCase p
         where p.report_status in (0, 5, 10)
           and p.out_time + '10 days' >= p.report_time
         group by P.BINGZHONG, p.work_no,p.source_dept_id

     )

select       ks.科室,
            ks.科室ID,
       pC.title as 病种名称,
               ys.医生,
               YS.医生ID,
             ysb.医生应上报人数,
               d.医生待上报人数,
               p.医生进入排除审批流程人数,
               yi.医生已排除人数,
               s.医生进入申请修改审批流程人数,
               t.医生填报端退回确认人数,
               sh.医生上报完成人数,
              yis.医生已上报人数,
              h.患者出院10日内已上报人数,
             hw.患者出院10日内未上报人数
         from disease_name pC
        full join   keshi ks   on PC.BINGZHONG = ks.BINGZHONG
         full join  yisheng ys  on pc.BINGZHONG =  ys.BINGZHONG
          full join  yingshangbao ysb on pc.BINGZHONG = ysb.BINGZHONG
        full join  daishangbao d on pC.BINGZHONG = d.BINGZHONG
         full join  paichushenpi p on pC.BINGZHONG = p.BINGZHONG
        full join  yipaichu yi on pC.BINGZHONG = yi.BINGZHONG
          full join  shenqingxiugai s on pC.BINGZHONG = s.BINGZHONG
           full join  tuihuiqueren t on pC.BINGZHONG = t.BINGZHONG
           full join shangbaowancheng sh on pC.BINGZHONG= sh.BINGZHONG
          full join  yishangbao yis on pC.BINGZHONG = yis.BINGZHONG
           full join  huanzhe10shangbao h on pC.BINGZHONG = h.BINGZHONG
           full join  huanzhe10weishangbao hw on pC.BINGZHONG = hw.BINGZHONG
group by  pc.title, ks.科室ID, ks.科室, ys.医生, YS.医生ID, ysb.医生应上报人数,
         d.医生待上报人数, p.医生进入排除审批流程人数, yi.医生已排除人数, s.医生进入申请修改审批流程人数,
         t.医生填报端退回确认人数, sh.医生上报完成人数, yis.医生已上报人数, h.患者出院10日内已上报人数, hw.患者出院10日内未上报人数;







--所在科室的医生的数据统计
select
        doc.name,
        v.source_doctor_id,
        pc.disease_code,
       pc.report_status,
       v.source_dept_id,
       v.source_ward_id,
       v.id,
       v.patient_name,
       v.bed_doc_name,
          pc.creation_by
from  mqm.patient_case pc
     left join  mqm.visit v
    on pc.visit_id = v.id
         left join mqm.doctor doc
    on v.source_doctor_id = doc.work_no

where
   v.source_doctor_id = 'han.mengming@synyi.com'
 and
    v.source_dept_id = '100'
and pc.report_status !='9'
and ((v.out_time :: date between '2000-01-01' and current_date)  or  v.reg_time :: date between '2000-01-01' and current_date);




--所在科室的医生的数据统计
select   pc.disease_code,
        (doc.name,
        v.source_doctor_id,
        pc.disease_code,
       pc.report_status,
       v.source_dept_id,
       v.source_ward_id,
       v.id,
       v.patient_name,
       v.bed_doc_name,
          pc.creation_by)
from  mqm.patient_case pc
     left join  mqm.visit v
    on pc.visit_id = v.id
         left join mqm.doctor doc
    on v.source_doctor_id = doc.work_no

where
      pc.disease_code = 'AF'
   --v.source_doctor_id = '0157'
--and (v.source_dept_id is null or v.source_dept_id = '0')
and pc.report_status !='9'
  --and pc.report_status not in (0,5,10)
and ((v.out_time :: date between '2000-01-01' and current_date)  or  v.reg_time :: date between '2000-01-01' and current_date)
--所在科室的医生的数据统计
select
       v.source_dept_id,
       v.source_doctor_id,
       pc.disease_code,
        (doc.name,
        v.source_doctor_id,
        pc.disease_code,
       pc.report_status,

       v.source_ward_id,
       v.id,
       v.patient_name,
       v.bed_doc_name,
          pc.creation_by)
from  mqm.patient_case pc
     left join  mqm.visit v
    on pc.visit_id = v.id
         left join mqm.doctor doc
    on v.source_doctor_id = doc.work_no

where
      pc.disease_code = 'AF'
   --v.source_doctor_id = '0157'
--and (v.source_dept_id is null or v.source_dept_id = '0')
and pc.report_status !='9'
  --and pc.report_status not in (0,5,10)
and ((v.out_time :: date between '2000-01-01' and current_date)  or  v.reg_time :: date between '2000-01-01' and current_date)