CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BJJHMK_BRLDXX
 (
 PATIENT_ID, ZYID00, VISIT_ID, ORIGIN_DEPT_CODE, ORIGIN_WARD_CODE, ORIGIN_BED_NO, 
CURR_DEPT_CODE, CURR_WARD_CODE, CURR_BED_NO, LOG_DATE_TIME

 )
 AS 
select b.BRID00       ,       
       b.ZYID00       ,
       b.BRRYCS       ,
       a.KSH000       ,           --转出科室
       a.BQH000       ,           --转出病区
       a.CH0000       ,           --转出床号
--       (select KSH000 from BQ_BRLDXX where ZYID00=a.ZYID00 and RowID>a.RowID and ROWnum=1),   --转入科室 
--       (select BQH000 from BQ_BRLDXX where ZYID00=a.ZYID00 and RowID>a.RowID and ROWnum=1),   --转入病区
--       (select CH0000 from BQ_BRLDXX where ZYID00=a.ZYID00 and RowID>a.RowID and ROWnum=1),   --转入床号
       (select KSH000 from BQ_BRLDXX where ZYID00=a.ZYID00 and (JSZT00 <>'请假' or jszt00 is null) and QSRQ00||QSSJ00>=a.QSRQ00||a.QSSJ00 and ROWnum=1),   --转入科室
       --JSZT00<>'请假' 改成 (JSZT00 <>'请假' or jszt00 is null) 20120918
       (select BQH000 from BQ_BRLDXX where ZYID00=a.ZYID00 and (JSZT00 <>'请假' or jszt00 is null) and QSRQ00||QSSJ00>=a.QSRQ00||a.QSSJ00 and ROWnum=1),   --转入病区
       (select CH0000 from BQ_BRLDXX where ZYID00=a.ZYID00 and (JSZT00 <>'请假' or jszt00 is null) and QSRQ00||QSSJ00>=a.QSRQ00||a.QSSJ00 and ROWnum=1),   --转入床号
       to_date(a.JSRQ00||a.JSSJ00,'YYYYMMDD HH24:MI:SS')    --转科时间
        from BQ_BRLDXX a,ZY_BRXXB0 b where a.ZYID00=b.ZYID00 and  (a.JSZT00 ='转出' or a.JSZT00 ='换出')
/