存储过程(工作随笔) 住院情况分析

USE [420923_2013]
GO
/****** Object:  StoredProcedure [dbo].[Pro_Zyqkfxb]    Script Date: 01/29/2013 09:31:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  proc [dbo].[Pro_Zyqkfxb]
@dateStart varchar(10),
@dateEnd varchar(10),
@Check varchar(10)-- 审核状态
as
declare @Sql varchar(3000);--主语句
declare @Sql_Main varchar(3000);--所有医院查询
declare @Sql_Mainxj varchar(200);--县级医院查询
declare @Sql_Mainxz varchar(200);--乡镇医院查询
declare @Sql_Mainsw varchar(200);--市外医院查询
declare @Sql_sumStr varchar(1000);--合计字段
declare @Sql_sumxz varchar(1000);--总合计乡镇
declare @Sql_sumxj varchar(1000);--总合计县级
declare @Sql_sumsw varchar(1000);--总合计市外
declare @Sql_sumzhj varchar(1000);--总合计
declare @Sql_Str varchar(2000);
declare @Sql_Str2 varchar(2000);
declare @intoTable varchar(3000);--存入临时表
declare @checked varchar(1);
declare @sumzfjj varchar(200);--所有机构总支付基金
declare @sumzrc varchar(200);--所有机构总人次

--declare @dateStart  varchar(10);
--declare @dateEnd varchar(10);
--declare @Check varchar(10)
--set @dateStart='2013-01-01'
--set @dateEnd='2013-01-24'
--set @Check=4

if  @Check=4 --
begin
set @checked=4
end
if  @Check=1 --
begin
set @checked=1
end
if  @Check=2 --
begin
set @checked=2
end
print (@checked)

--所有机构总支付基金
select @sumzfjj=sum(a.CheckFee) from dbo.v_cbzybxdj a,V_CbZyZdb b
 where a.billcode=b.billcode and a.billno=b.billno
and WriteDate>=@dateStart  and WriteDate<=@dateEnd and CheckStatus=@checked
print(@sumzfjj)
--所有机构总人次
select @sumzrc=
 count(1) from dbo.s_CbZyBxDj a,s_CbZyZdb b where a.billcode=b.billcode and
 a.billno=b.billno and WriteDate >= @dateStart  and WriteDate <=@dateEnd and CheckStatus=@checked
print(@sumzrc)

set @Sql_Str='
select HospitalCode orgcode,count(1)住院人次,sum(TotalFee)住院总费用,sum(a.canfreefee)政策内费用,sum(daycount)住院天数,
sum(freefee)基金补偿,null 医院承担,sum(freefee-a.CheckFee)核减金额 ,sum(a.CheckFee)基金支付,sum(daycount)/count(1)例均天数,
sum(TotalFee)/count(1)例均费用,sum(TotalFee)/sum(daycount)日均费用
,sum(freefee)/count(1)例均补偿,convert(decimal(18,2),sum(freefee)/sum(TotalFee)*100)补偿率,
convert(decimal(18,2),sum(freefee)/sum(a.canfreefee)*100)政策内补偿率,
sum(a.CheckFee)/'+@sumzfjj+'*100 基金流向,
count(1)/'+@sumzrc+'患者流向
 from dbo.v_cbzybxdj  a,v_CbZyZdb  b
 where a.billcode=b.billcode and a.billno=b.billno  and  CheckStatus='''+@checked+'''
 and WriteDate >='''+@dateStart+'''  and  WriteDate <='''+@dateEnd+'''   group by HospitalCode 
'
set @Sql_Str2='
(select HospitalCode orgcode,sum(case when  left(FeeTypeCode,3)=''000'' then a.fee else 0 end)药品总费用,
sum(case when  FeeTypeCode in (''000204'',''000202'',''000102'')then a.fee else 0 end)自费药品,
sum(case when  left(FeeTypeCode,4)=''0201'' or  left(FeeTypeCode,4)=''0301'' then a.fee else 0 end)检查总费用,
sum(case when  FeeTypeCode in (''020102'',''030102'')then a.fee else 0 end)自费检查
 from  dbo.v_CbZyBxDjZb a,dbo.v_CbZyBxDj b, v_CbZyZdb c where a.orgcode=b.orgcode and
 a.coopmedcode=b.coopmedcode and a.idno=b.idno and a.diagno=b.diagno
 and  c.billcode=b.billcode and c.billno=b.billno and WriteDate >='''+@dateStart+'''
 and WriteDate <='''+@dateEnd+''' and  CheckStatus='''+@checked+'''  group by b.HospitalCode)
 '
 
 --所有医院查询
set @Sql_Main='select   c.OrgName, a.*,b.药品总费用,自费药品,检查总费用,自费检查 ,
convert(decimal(18,2),自费药品/药品总费用*100) 药品自费率,convert(decimal(18,2),自费检查/检查总费用*100) 检查自费率
from ('+@Sql_Str+')a, ('+@Sql_Str2+')b,(select * from p_ylfwdw where Grade in (1,2,3)and OrgType=2 ) c
 where a.orgcode=b.orgcode and a.orgcode=c.orgcode '

--删掉临时表
if exists (select * from sysobjects where id = object_id(N'temp_Pro_Zyqkfxb'))
 drop table temp_Pro_Zyqkfxb

--插入临时表
set @intoTable='
select * into temp_Pro_Zyqkfxb  from  ('+ @Sql_Main+') a
'
print(@intoTable)
exec(@intoTable)

--乡镇医院查询
set @Sql_Mainxz='select a. * from temp_Pro_Zyqkfxb a,(select * from p_ylfwdw where Grade=1 and OrgType=2 ) b where a.orgcode=b.orgcode'
--县级医院查询
set @Sql_Mainxj='select  a.* from temp_Pro_Zyqkfxb a,(select * from p_ylfwdw where Grade=2 and OrgType=2 ) b where a.orgcode=b.orgcode'
--市外医院查询
set @Sql_Mainsw='select  a.* from temp_Pro_Zyqkfxb a,(select * from p_ylfwdw where Grade=3 and OrgType=2 ) b where a.orgcode=b.orgcode'

--合计字段 'select ''总合计'' 合计 ,convert (varchar(12),null) org ,
set @Sql_sumStr='sum(isnull(住院人次,0))住院人次,sum(住院总费用)住院总费用,
sum(政策内费用)政策内费用,sum(住院天数)住院天数,sum(基金补偿)基金补偿,sum(isnull(医院承担,0))医院承担,sum(核减金额)核减金额,
sum(基金支付)基金支付,sum(例均天数)例均天数,sum(例均费用)例均费用,sum(日均费用)日均费用,sum(例均补偿)例均补偿,sum(补偿率)补偿率,sum(政策内补偿率)政策内补偿率,
sum(基金流向)基金流向,sum(患者流向)患者流向,sum(药品总费用)药品总费用,sum(自费药品)自费药品,sum(检查总费用)检查总费用,sum(自费检查)自费检查,sum(药品自费率)药品自费率,sum(检查自费率)检查自费率'

--总合计
set @Sql_sumzhj = 'select ''总合计'' 合计 ,convert (varchar(12),420923999999) org ,'+@Sql_sumStr+' from temp_Pro_Zyqkfxb '

 --县级医院合计
set @Sql_sumxj = 'select ''县级小计'' 合计 ,convert (varchar(12),420923000099) org ,'+@Sql_sumStr+' from ('+@Sql_Mainxj+')a'

 --乡镇医院合计
set @Sql_sumxz = 'select ''乡镇小计'' 合计 ,convert (varchar(12),420923200001) org ,'+@Sql_sumStr+' from ('+@Sql_Mainxz+')a'
 --市外医院合计
set @Sql_sumsw = 'select ''市外小计'' 合计 ,convert (varchar(12),420923999991) org ,'+@Sql_sumStr+' from ('+@Sql_Mainsw+')a'

set @sql='select * from temp_Pro_Zyqkfxb a
union all select * from ('+ @Sql_sumxz+')d
union all select * from ('+ @Sql_sumxj+')b
union all select * from ('+ @Sql_sumsw+')c
union all select * from ('+ @Sql_sumzhj+')c
order by orgcode'
print(@sql)
exec(@sql)

-- exec [Pro_Zyqkfxb] '2013-01-01','2013-01-24',4