记录一下实际占用总床日数sql语句

weixin_55114864

已于 2024-03-21 20:49:13 修改

阅读量438

点赞数 3

分类专栏： SQL 文章标签： sql linux 数据库

于 2024-03-07 15:03:52 首次发布

本文链接：https://blog.csdn.net/weixin_55114864/article/details/136534373

版权

SQL 专栏收录该内容

2 篇文章

订阅专栏

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;