CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_KSRZQKTJ
 (
 KCS000, MZSZRS, BQJZKCS, GXKCS0, DRKRS0, BQDRJZRS, KSYYS0, KSH000

 )
 AS 
select 
     (select count(*) from BM_CWK000 where BQH000 = b.BQH000 and KSH000 = b.KSH000 and CWZT00='0' and ZYID00 is null) KCS000,
     decode((select nvl(trim(VALUE0),'0') from XT_XTCS00 where NAME00='YS_SZJMYYXXJGSJ'),'0',
            (select count(*) from YS_BRZYSQ where KSH000 = b.KSH000 and ZT0000 ='1' and (ZYID00 = 0 or ZYID00 is null)),
            (select count(*) from YS_BRZYSQ where KSH000 = b.KSH000 and ZT0000 = '1' and (ZYID00 = 0 or ZYID00 is null) and SQRQ00 >= to_char(sysdate - (select nvl(trim(VALUE0),'0') from XT_XTCS00 where NAME00='YS_SZJMYYXXJGSJ') / 24, 'yyyymmddhh24:mi:ss'))
           ) MZSZRS,
     (select count(*) from BM_CWK000 where BQH000 = b.BQH000 and CWZT00='0' and ZYID00 is null and JZBZ00 = 1) BQJZKCS,
     (select count(*) from BM_CWK000 where KSH000 = b.KSH000 and CWZT00='0' and ZYID00 is null and GXBZ00 = 1) GXKCS0,
     (select count(*) from YS_BRZYSQ a  where KSH000 = b.KSH000 and (ZYID00 != 0 or ZYID00 is not null) and exists (select 1 from ZY_BRXXB0 where ZYID00 = a.ZYID00 and BRZT00 = '1')) DRKRS0,
     decode((select nvl(trim(VALUE0),'0') from XT_XTCS00 where NAME00='ZY_MZYSZYSQRQYXTS'),'0',
            (select count(*) from YS_BRZYSQ where BQH000 = b.BQH000 and ZT0000 = '4'and RYFS00 = '¼±Õï'),
            (select count(*) from YS_BRZYSQ where BQH000 = b.BQH000 and ZT0000 = '4'and RYFS00 = '¼±Õï' and SQRQ00 >= to_char(sysdate - (select nvl(trim(VALUE0),'0') from XT_XTCS00 where NAME00='ZY_MZYSZYSQRQYXTS'), 'yyyymmdd'))
           ) BQDRJZRS,
    -- (select count(*) from YS_BRZYSQ where BQH000 = b.BQH000 and ZT0000 = '4'and RYFS00 = '¼±Õï') BQDRJZRS,
     decode((select nvl(trim(VALUE0),'0') from XT_XTCS00 where NAME00='YS_SZJMYYXXJGSJ'),'0',
            (select count(*) from YS_BRZYSQ where KSH000 = b.KSH000 and ZT0000 in ('1','2','3') and (ZYID00 = 0 or ZYID00 is null)),
            (select count(*) from YS_BRZYSQ where KSH000 = b.KSH000 and ZT0000 in ('1', '2', '3') and (ZYID00 = 0 or ZYID00 is null) and SQRQ00 >= to_char(sysdate - (select nvl(trim(VALUE0),'0') from XT_XTCS00 where NAME00='YS_SZJMYYXXJGSJ') / 24, 'yyyymmddhh24:mi:ss'))
           ) KSYYS0,
      b.KSH000
from BM_BQKS00 b
/