create or replace view vw_zy_bqcwsy as
select
  a.BQH000,            --病区号
  b.BMMC00 as BQMC00,  --病区名称,
  b.YYID00 as BQYYID,  --病区所属医院ID
  count(1) as ZCS000,  --总床数,
 (select count(1)from BM_CWK000 where BQH000=a.BQH000 and KCBZ00 =1 and CWXZ00 !=2) as KCS000,--空床数
  0 as WLRS00,         --未离人数
 nvl(max(C.MRCYRS),0) MRCYRS,  --明日出院人数
 (select count(1)from ZY_BRXXB0 where DQBQ00=a.BQH000 and BRZT00 = '21' and RYCWH0 is null ) as DZRRS0,  --待转入人数
 (select count(1)from ZY_BRXXB0 where DQBQ00=a.BQH000 and BRZT00 = '20') as zkrs00,   --在科病人
 (select count(1)from ZY_BRXXB0 where DQBQ00=a.BQH000 and BRZT00 in ('0','1') and RYRQ00>=to_char(sysdate-7,'YYYYMMDD')) as DRYRS0,  --待入院，收费处做登记，病区未收治
 (select count(distinct BRID00) from YS_BRZYSQ where ZT0000='3' and JSBZ00='0' and nvl(ZYID00,0)<=0
   and BQH000=a.BQH000 and TZYXRQ>=to_char(sysdate,'YYYYMMDD')) as XBR000--新病人(预约已通知)
from BM_CWK000 a,BM_BMBM00 b,
  (
  select BQH000,count(1) MRCYRS from BQ_YJYZ00 where YZZT00='3' and QYRQ00=to_char(sysdate,'YYYYMMDD') and ZLXMJC like '明日出院%'
   group by BQH000
  ) c
where a.BQH000=b.BMBH00 and a.CWXZ00<>'2' and a.BQH000=c.BQH000(+)
group by nvl(b.PXXH00,99999),a.BQH000,b.BMMC00,b.YYID00
;