病人信息查询

SELECT zy_brry.ZYHM as zyhm,

BA_BRDA.RYCS as RYCS,

zy_brry.BRXM as BRXM,

zy_brry.BRXB as BRXB,

zy_brry.CSNY as CSNY,

ba_brda.DWDH as DWDH,

BA_BRDA.LXDH as LXDH,

BA_BRSY.RYRQ as RYRQ,

gy_ksdm.ksmc as ryks,

BA_BRSY.CYRQ as cyrq,

gy_ksdm.ksmc as cyks,

gy_ygdm.ygxm as zzys,

gy_jbbm.jbmc as zyzd

FROM BA_BRDA,

BA_BRSY,

gy_ksdm,

gy_ygdm,

gy_jbbm,

zy_brry

WHERE (BA_BRSY.ZYH = BA_BRDA.ZYH) and

(BA_BRSY.ryks=gy_ksdm.ksdm)and

(BA_BRSY.cyks=gy_ksdm.ksdm)and

(BA_BRSY.zzys=gy_ygdm.ygdm)and

(BA_BRSY.zyzd=gy_jbbm.jbxh)and

(BA_BRSY.ZYH = zy_brry.ZYH);

//

//select zyhm,brxm,cyrq from zy_brry

//where cyrq >= to_date('2011-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and // cyrq <= to_date('2011-07-31 23:59:59','yyyy-mm-dd hh24:mi:ss') and // cypb <> 99

//order by zyhm ;

//门诊处方

select ms_cf01.cfhm,

ms_cf01.brxm,

gy_ksdm.ksmc,

gy_ygdm.ygxm,

ms_cf01.kfrq,

yk_typk.ypmc,

yk_typk.ypgg,

ms_cf02.ypsl,

ms_cf02.ypdj,

ms_cf02.hjje

from ms_cf01,

ms_cf02,

yk_typk,

gy_ksdm,

gy_ygdm

where ms_cf01.cfsb = ms_cf02.cfsb and

ms_cf01.ksdm = gy_ksdm.ksdm and

ms_cf01.ysdm = gy_ygdm.ygdm and

ms_cf01.kfrq >= to_date('2011-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and ms_cf01.kfrq < to_date('2011-10-20 23:59:59','yyyy-mm-dd hh24:mi:ss') and ms_cf01.zfpb = 0 and

ms_cf01.fphm is not null and

ms_cf01.yfsb =1 and

ms_cf02.ypxh = yk_typk.ypxh

order by cfhm;

//Ⅰ类切口

//select emr_bl_bl01.brxm,

// emr_bl_bl01.brbh as zyhm,

// emr_bl_basysj.xmmc,

// emr_bl_basysj.xmqz

// from emr_bl_bl01,

// emr_bl_basysj

//where (emr_bl_basysj.xmqz like 'Ⅰ' or emr_bl_basysj.xmxh = 215) and // emr_bl_bl01.jzhm = emr_bl_basysj.jzhm and

// emr_bl_bl01.cjsj >= to_date('2011-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and

// emr_bl_bl01.cjsj <= to_date('2011-08-31 23:59:59','yyyy-mm-dd hh24:mi:ss') and

// emr_bl_bl01.bllb = 2000001 and

// emr_bl_bl01.blzt <> 9 and

// emr_bl_basysj.xmqz is not null

//order by emr_bl_bl01.brbh;

//门诊抗菌药物使用情况

select yk_typk.ypmc,

yk_typk.ypgg,

yk_ypsx.sxmc,

yk_typk.ypdw,

sum(ms_cf02.ypsl),

sum(ms_cf02.hjje)

from ms_cf01,

ms_cf02,

yk_typk,

yk_ypsx

where ms_cf01.cfsb = ms_cf02.cfsb and

ms_cf01.kfrq >= to_date('2011-07-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and ms_cf01.kfrq <= to_date('2011-07-31 23:59:59','yyyy-mm-dd hh24:mi:ss') and ms_cf01.zfpb = 0 and

ms_cf02.ypxh = yk_typk.ypxh and

yk_typk.ypsx = yk_ypsx.ypsx and

ms_cf02.ypxh in(select ypxh from emr_wh_ylqxyp)

group by yk_typk.ypmc,

yk_typk.ypgg,

yk_ypsx.sxmc,

yk_typk.ypdw

order by yk_typk.ypmc;

//住院半年抗菌药物使用情况

select yk_typk.ypmc,

yk_typk.ypgg,

yk_ypsx.sxmc,

yk_typk.ypdw,

zy_fymx.fydj,

sum(zy_fymx.fysl) as fysl,

sum(zy_fymx.zjje)

from zy_fymx,

yk_typk,

yk_ypsx

where ypcd > 0 and

jfrq >= to_date('2011-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and jfrq <= to_date('2011-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss') and yk_typk.ypxh = zy_fymx.fyxh and

yk_typk.ksbz=1 and

yk_typk.ypsx = yk_ypsx.ypsx

group by yk_typk.ypmc,

yk_typk.ypgg,

yk_ypsx.sxmc,

yk_typk.ypdw,

zy_fymx.fydj;

//住院天数统计

//select ksmc,

// (cyrq - ryrq) as zyts

//from zy_brry ,

// gy_ksdm

//where brbq in(720,536,505,510,517,518,530,537,538,507,508) and

// cyrq >= to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and // cyrq <= to_date('2011-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and // gy_ksdm.ksdm = zy_brry.brbq

//order by brbq;

select ms_cf01.cfhm,

ms_cf01.brxm,

gy_ksdm.ksmc,

gy_ygdm.ygxm,

ms_cf01.kfrq,

yk_typk.ypmc,

yk_typk.ypgg,

ms_cf02.ypsl,

ms_cf02.ypdj,

ms_cf02.hjje

from ms_cf01,

ms_cf02,

yk_typk,

gy_ksdm,

gy_ygdm

where ms_cf01.cfsb = ms_cf02.cfsb and

ms_cf01.ksdm = gy_ksdm.ksdm and

ms_cf01.ysdm = gy_ygdm.ygdm and

ms_cf01.kfrq >= to_date('2011-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and ms_cf01.kfrq <= to_date('2011-08-31 23:59:59','yyyy-mm-dd hh24:mi:ss') and ms_cf01.zfpb = 0 and

ms_cf01.fphm is not null and

ms_cf01.yfsb =1 and

ms_cf02.ypxh = yk_typk.ypxh

order by cfhm;

出院病人费用查询

SELECT zy_brry.zyhm,

sum( case when GY_SFXM.ZYGB=16 then ZY_FYMX.ZJJE else 0 end ) as cw, sum( case when GY_SFXM.ZYGB=30 then ZY_FYMX.ZJJE else 0 end ) as zc, sum( case when GY_SFXM.ZYGB=1 then ZY_FYMX.ZJJE else 0 end ) as xy, sum( case when GY_SFXM.ZYGB=3 then ZY_FYMX.ZJJE else 0 end ) as zy, sum( case when GY_SFXM.ZYGB=7 then ZY_FYMX.ZJJE else 0 end ) as hy, sum( case when GY_SFXM.ZYGB=38 then ZY_FYMX.ZJJE else 0 end ) as hl, sum( case when GY_SFXM.ZYGB=6 then ZY_FYMX.ZJJE else 0 end ) as sx, sum( case when GY_SFXM.ZYGB=15 then ZY_FYMX.ZJJE else 0 end ) as mz, sum( case when GY_SFXM.ZYGB=16 then ZY_FYMX.ZJJE else 0 end ) as qtfy FROM GY_SFXM,

ZY_FYMX,

zy_brry

WHERE ( ZY_FYMX.FYXM = GY_SFXM.SFXM) and

( zy_fymx.zyh = ZY_brry.zyh ) and

(zy_brry.cypb=8) and

(GY_SFXM.ZYGB in (16,30,1,3,7,38,6,15,45)) and

( zy_brry.cyrq <= to_date('2011-10-07 23:59:00','yyyy-mm-dd hh24:mi:ss')) GROUP BY zy_brry.zyhm

order by zy_brry.zyhm;

//出院病人用药查询

出院病人药品统计

select zy_brry.zyhm,

zy_brry.brxm,

gy_ksdm.ksmc,

zy_brry.ryrq,

zy_brry.cyrq,

zy_fymx.fymc,

yk_typk.ypgg,

zy_fymx.fydj,

sum(zy_fymx.fysl),

sum(zy_fymx.zjje)

from zy_brry,

zy_fymx,

yk_typk,

gy_ksdm

where zy_brry.zyh=zy_fymx.zyh and

zy_fymx.fyxh = yk_typk.ypxh and

gy_ksdm.ksdm = zy_fymx.fyks and

zy_fymx.ypcd<>0 and

zy_fymx.zxks=3 and

zy_brry.cyrq>=to_date('2011-10-21 00:00:00','yyyy-mm-dd hh24:mi:ss') and zy_brry.cyrq <= to_date('2011-11-20 23:59:59','yyyy-mm-dd hh24:mi:ss')

group by zy_brry.zyhm,

zy_brry.brxm,

gy_ksdm.ksmc,

zy_brry.ryrq,

zy_brry.cyrq,

zy_fymx.fymc,

yk_typk.ypgg,

zy_fymx.fydj order by zy_brry.zyhm ;

急诊

select zy_brry.zyhm,

zy_brry.brxm,

zy_brry.ryrq,

gy_ygdm.ygxm

from zy_brry,

gy_ygdm

where (zy_brry.szys=gy_ygdm.ygdm) and

zy_brry.szys in (select gy_ygdm.ygdm from gy_ygdm where ksdm=103) and

zy_brry.ryrq >= to_date('2011-08-21 20:00:00','yyyy-mm-dd hh24:mi:ss') and zy_brry.ryrq <= to_date('2011-09-20 08:00:00','yyyy-mm-dd hh24:mi:ss') order by zy_brry.ryrq;

健康热线

SELECT zy_brry.ZYHM as zyhm,

BA_BRDA.RYCS as RYCS,

zy_brry.BRXM as BRXM,

zy_brry.BRXB as BRXB,

zy_brry.CSNY as CSNY,

ba_brda.DWDH as DWDH,

BA_BRDA.LXDH as LXDH,

BA_BRSY.RYRQ as RYRQ,

gy_ksdm.ksmc as ryks,

BA_BRSY.CYRQ as cyrq,

gy_ksdm.ksmc as cyks,

gy_ygdm.ygxm as zzys,

gy_jbbm.jbmc as zyzd

FROM BA_BRDA,

BA_BRSY,

gy_ksdm,

gy_ygdm,

gy_jbbm,

zy_brry

WHERE (BA_BRSY.ZYH = BA_BRDA.ZYH) and

(BA_BRSY.ryks=gy_ksdm.ksdm)and

(BA_BRSY.cyks=gy_ksdm.ksdm)and

(BA_BRSY.zzys=gy_ygdm.ygdm)and

(BA_BRSY.zyzd=gy_jbbm.jbxh)and

(BA_BRSY.ZYH = zy_brry.ZYH) and

BA_BRSY.CYRQ >= to_date('2011-09-24 00:00:00','yyyy-mm-dd hh24:mi:ss') and

BA_BRSY.CYRQ <= to_date('2011-10-09 23:00:00','yyyy-mm-dd hh24:mi:ss') order by BA_BRSY.CYRQ;

挂号金额统计

select gy_ygdm.ygxm,

sum (ms_ghmx.blje) as hjze

from gy_ygdm,

ms_ghmx

where gy_ygdm.ygdm=ms_ghmx.czgh and

ms_ghmx.blje<>0

ms_ghmx.jzrq >= to_date('2011-04-21 23:59:00','yyyy-mm-dd hh24:mi:ss')and ms_ghmx.jzrq <= to_date('2011-10-29 23:59:00','yyyy-mm-dd hh24:mi:ss') group by gy_ygdm.ygxm,ms_ghmx.blje

order by gy_ygdm.ygxm,ms_ghmx.blje;

复诊病人查询(科室）

select ms_brda.mzhm,

ms_brda.brxm,

trunc((months_between(trunc(sysdate,'dd'),ms_brda.csny))/12) as nl, (Case WHEN ms_brda.brxb =1 THEN '男' WHEN brxb =2 THEN '女' ELSE '未知' end) as xb,

gy_ksdm.ksmc,

gy_ygdm.ygxm,

ys_mz_jzls.kssj as jzsj,

ys_mz_jzls.fzrq

from ys_mz_jzls,

gy_ksdm,

gy_ygdm,

ms_brda

where ys_mz_jzls.ksdm=gy_ksdm.ksdm and

ys_mz_jzls.ysdm=gy_ygdm.ygdm and

ys_mz_jzls.brbh=ms_brda.brid and

ys_mz_jzls.fzrq is not null and

ys_mz_jzls.kssj>= to_date('2011-04-21 00:00:00','yyyy-mm-dd hh24:mi:ss')and ys_mz_jzls.kssj<= to_date('2011-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss')and ys_mz_jzls.ksdm=116

order by ys_mz_jzls.kssj;

//住院病人含住院诊断查询

select zy_brry.zyhm,

zy_brry.brxm,

trunc((months_between(trunc(sysdate,'dd'),csny))/12) as nl,

(Case WHEN brxb =1 THEN '男' WHEN brxb =2 THEN '女' ELSE '未知' end) as xb, zy_brry.dwdh,

zy_brry.jtdh,

ys_zy_jbzd.jbmc,

gy_ksdm.ksmc,

zy_brry.ryrq,

zy_brry.cyrq,

floor(to_date(zy_brry.cyrq) - to_date(zy_brry.ryrq)) as zyts,

floor(to_date(cyrq) - to_date(ryrq)) + 1 as zyts,

Case zy_brry.cyfs WHEN 1 THEN '治愈' WHEN 2 THEN '好转' WHEN 3 THEN '未愈' WHEN 4 THEN '死亡' ELSE '自动出院' end as zgqk

from zy_brry,ys_zy_jbzd,gy_ksdm

where ys_zy_jbzd.jzhm = zy_brry.zyh and

zy_brry.brks = gy_ksdm.ksdm and

ys_zy_jbzd.zdlb = '出院诊断'and

ys_zy_jbzd.zfbz =0 and

ys_zy_jbzd.tjbz =1 and

zy_brry.cypb in (1,8) and

zy_brry.cyrq>=to_date('2011-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and zy_brry.cyrq<=to_date('2011-11-20 23:59:59','yyyy-mm-dd hh24:mi:ss' ) and ys_zy_jbzd.jlbh in (select min(jlbh) from ys_zy_jbzd a where a.jzhm =zy_brry.zyh and a.zdlb = '出院诊断'and a.zfbz =0 and a.tjbz =1)

order by gy_ksdm.ksmc,zy_brry.zyhm ;

切口等级1

//select zy_brry.zyhm,

// zy_brry.brxm,

// trunc((months_between(trunc(sysdate,'dd'),csny))/12) as nl,

// (Case WHEN brxb =1 THEN '男' WHEN brxb =2 THEN '女' ELSE '未知' end) as xb,

// ys_zy_jbzd.jbmc,

// sm_ssjl.qkdj,

// gy_ksdm.ksmc,

// zy_brry.ryrq,

// zy_brry.cyrq,

// floor(to_date(zy_brry.cyrq) - to_date(zy_brry.ryrq)) as zyts, // floor(to_date(cyrq) - to_date(ryrq)) + 1 as zyts,

// Case zy_brry.cyfs WHEN 1 THEN '治愈' WHEN 2 THEN '好转' WHEN 3 THEN '未愈' WHEN 4 THEN '死亡' ELSE '自动出院' end as zgqk

// from zy_brry,ys_zy_jbzd,gy_ksdm,sm_ssjl

//where ys_zy_jbzd.jzhm = zy_brry.zyh and

// zy_brry.brks = gy_ksdm.ksdm and

// zy_brry.zyh=sm_ssjl.zyh and

// ys_zy_jbzd.zdlb = '出院诊断'and

// ys_zy_jbzd.zfbz =0 and

// sm_ssjl.qkdj=1 and

// ys_zy_jbzd.tjbz =1 and

// zy_brry.cypb in (1,8) and

// zy_brry.cyrq>=to_date('2011-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and // zy_brry.cyrq<=to_date('2011-11-20 23:59:59','yyyy-mm-dd hh24:mi:ss' ) and // ys_zy_jbzd.jlbh in (select min(jlbh) from ys_zy_jbzd a where a.jzhm =zy_brry.zyh and a.zdlb = '出院诊断'and a.zfbz =0 and a.tjbz =1)

// order by gy_ksdm.ksmc,zy_brry.zyhm ;

//

//select zy_brry.zyhm,

// zy_brry.brxm,

// trunc((months_between(trunc(sysdate,'dd'),csny))/12)as nl,

// (Case WHEN brxb =1 THEN '男' WHEN brxb =2 THEN '女' ELSE '未知' end) as xb, // gy_ksdm.ksmc,

// ys_zy_jbzd.jbmc,

// sm_ssjl.qkdj

//from zy_brry,gy_ksdm,sm_ssjl,ys_zy_jbzd

//where zy_brry.brks = gy_ksdm.ksdm and

// zy_brry.zyh=sm_ssjl.zyh and

// sm_ssjl.qkdj=1 and

//// ys_zy_jbzd.jzhm = zy_brry.zyh and

//// ys_zy_jbzd.tjbz =1 and

//// ys_zy_jbzd.zdlb = '出院诊断'and

////ys_zy_jbzd.jlbh in (select min(jlbh) from ys_zy_jbzd a where a.jzhm =zy_brry.zyh and a.zdlb = '出院诊断'and a.zfbz =0 and a.tjbz =1) and

//zy_brry.cyrq>=to_date('2011-10-21 00:00:00','yyyy-mm-dd hh24:mi:ss') and //zy_brry.cyrq<=to_date('2011-11-20 23:59:59','yyyy-mm-dd hh24:mi:ss' ) //order by gy_ksdm.ksmc,zy_brry.zyhm ;

////