SELECT
--实际占用总床日数
(
SELECT
sum(
CASE
WHEN bivr.STATUS >= 8
AND bivi.OUT_DEPT_TIME < to_date( '2024-03-01', 'YYYY-MM-DD' ) THEN
bivc.days ELSE (
CASE
WHEN TRUNC( to_date( '2024-02-29', 'YYYY-MM-DD' ) ) - TRUNC( bivi.ADMISSION_DEPT_TIME ) = 0 THEN
1 ELSE TRUNC( to_date( '2024-02-29', 'YYYY-MM-DD' ) ) - TRUNC( bivi.ADMISSION_DEPT_TIME )
END
)
END
) AS bed_count
FROM
hb_basic.BIZ_IP_VISIT_REGISTER bivr
JOIN hb_basic.BIZ_IP_VISIT_CLINIC bivc ON bivr.id = bivc.VISIT_ID
JOIN hb_basic.BIZ_IP_VISIT_INOUT bivi ON bivi.visit_id = bivr.id
WHERE
bivr.flag = 1
AND bivr.INVALID = 0
AND bivi.ADMISSION_DEPT_TIME >= to_date( '2024-02-01', 'YYYY-MM-DD' )
AND bivi.ADMISSION_DEPT_TIME < to_date( '2024-03-01', 'YYYY-MM-DD' )
) AS "实际占用总床日数",
(---出院者占用床位数
SELECT
sum( bivc.days ) AS bed_count
FROM
hb_basic.BIZ_IP_VISIT_REGISTER bivr
JOIN hb_basic.BIZ_IP_VISIT_CLINIC bivc ON bivr.id = bivc.VISIT_ID
JOIN hb_basic.BIZ_IP_VISIT_INOUT bivi ON bivi.visit_id = bivr.id
WHERE
bivr.flag = 1
AND bivr.INVALID = 0
AND bivr.STATUS IN ( 8, 32 )
AND bivi.out_dept_time >= to_date( '2024-02-01', 'YYYY-MM-DD' )
AND bivi.out_dept_time < to_date( '2024-03-01', 'YYYY-MM-DD' )
) AS "出院者占用床位数",
-- 总诊疗人次(门诊、住院)
(select sum(A.all_count)
from (
select count(*) as all_count from hb_basic.biz_op_visit where flag=1 and INVALID =0
and create_time>=to_date('2024-02-01','YYYY-MM-DD')
and create_time<to_date('2024-03-01','YYYY-MM-DD')
union all
select count(*) as all_count from hb_basic.BIZ_IP_VISIT_REGISTER bivr join hb_basic.BIZ_IP_VISIT_INOUT bivi on
bivr.id = bivi.VISIT_ID
where bivr.flag=1 and bivr.INVALID =0
and bivi.ADMISSION_DEPT_TIME >=to_date('2024-02-01','YYYY-MM-DD')
and bivi.ADMISSION_DEPT_TIME<to_date('2024-03-01','YYYY-MM-DD')
)A) as "总诊疗人次(门诊、住院)",
(-- 普通门诊人次数
select count(*) as all_count from hb_basic.biz_op_visit where flag=1 and INVALID =0
and create_time>=to_date('2024-02-01','YYYY-MM-DD')
AND REGISTER_TYPE_NAME NOT LIKE '%急诊%' and status>=1
and create_time<to_date('2024-03-01','YYYY-MM-DD')) as "普通门诊人次数",
(--- 中医诊疗人次数(中医科)
select count(*) as all_count from hb_basic.biz_op_visit where flag=1 and INVALID =0
and dept_id='HWD00034'
and create_time>=to_date('2024-02-01','YYYY-MM-DD')
and create_time<to_date('2024-03-01','YYYY-MM-DD')) as "中医诊疗人次数(中医科)",
(---急诊人次数
select count(*) from hb_basic.biz_op_visit where flag=1 and INVALID =0 and REGISTER_TYPE_NAME like '%急诊%'
and create_time>=to_date('2024-02-01','YYYY-MM-DD')
and create_time<to_date('2024-03-01','YYYY-MM-DD')) as "急诊人次数",
(--出院人次数
select count(*) as all_count from hb_basic.BIZ_IP_VISIT_REGISTER bivr join hb_basic.BIZ_IP_VISIT_INOUT bivi on
bivr.id = bivi.VISIT_ID
where bivr.flag=1 and bivr.INVALID =0 and bivr.STATUS in(8,32)
and bivi.out_dept_time>=to_date('2024-02-01','YYYY-MM-DD')
and bivi.out_dept_time<to_date('2024-03-01','YYYY-MM-DD')) as "出院人次数",
(--总死亡人数、住院死亡人数
select count(*) as all_count from hb_basic.BIZ_IP_VISIT_REGISTER bivr join hb_basic.BIZ_IP_VISIT_INOUT bivi on
bivr.id = bivi.VISIT_ID
join hb_basic.BIZ_IP_VISIT_CLINIC bivc on bivc.visit_id = bivr.id
where bivr.flag=1 and bivr.INVALID =0 and bivc.STATUS =9
and bivi.out_dept_time>=to_date('2024-02-01','YYYY-MM-DD')
and bivi.out_dept_time<to_date('2024-03-01','YYYY-MM-DD')) as "总死亡人数、住院死亡人数",
(--健康检查人次 (体检科)
select count(*) as all_count from hb_basic.biz_op_visit where flag=1 and INVALID =0
and dept_id='HWD00042'
and create_time>=to_date('2024-02-01','YYYY-MM-DD')
and create_time<to_date('2024-03-01','YYYY-MM-DD')) as "健康检查人次 (体检科)"
FROM
dual;
记录一下实际占用总床日数sql语句
于 2024-03-07 15:03:52 首次发布