SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create function ConvertNumberToChinese(@ varchar(100)) returns varchar(100) as begin --declare @ varchar(100) --set @ = '101140101031013' declare @s varchar(100) set @s = '' declare @p integer set @p = 0 declare @m integer set @m = len(@) % 4 declare @k integer if @m > 0 begin set @k = len(@)/4 + 1 end else begin set @k = len(@)/4 end declare @i integer set @i = @k while (@i > 0) begin --out declare @L integer set @L = 4 if @i = @k and @m != 0 begin set @L = @m end declare @ss varchar(4) set @ss = substring(@,@p+1,@L) declare @ll integer set @ll = len(@ss) --inner declare @j integer set @j = 0 while (@j < @ll) --inner begin --inner declare @n integer set @n = cast(substring(@ss,@j+1,1) as integer) declare @num varchar(2) set @num =( select Num from ( select 0 as id,'零' as Num union all select 1,'壹' union all select 2,'贰' union all select 3,'叁' union all select 4,'肆' union all select 5,'伍' union all select 6,'陆' union all select 7,'柒' union all select 8,'捌' union all select 9,'玖' ) Nums where id = @n ) if @n = 0 begin if @j < @ll - 1 and cast(substring(@ss,(@j+1)+1,1) as integer) > 0 and right(@ss,1) != @num begin set @s = @s + @num end end else begin set @s = @s + @num + ( select digit from ( select 0 as id,'' as digit union all select 1,'拾' union all select 2,'佰' union all select 3,'仟' ) digits where id = @ll - @j - 1 ) end set @j = @j + 1 --inner end --inner set @p = @p + @L
declare @unit varchar(10) set @unit = ( select Unit from ( select 0 as id,'' as Unit union all select 1,'[万]' union all select 2,'[亿]' union all select 3,'[万亿]' ) Units where id = @i - 1 ) if @i < @k begin if cast(@ss as integer) != 0 begin set @s = @s + @unit end end else begin set @s = @s + @unit end set @i = @i - 1 -- out end --out return @s /**//* --Test: select dbo.ConvertNumberToChinese('40000000000') ,dbo.ConvertNumberToChinese('40000000001') ,dbo.ConvertNumberToChinese('400000010000') ,dbo.ConvertNumberToChinese('40101031013') ,dbo.ConvertNumberToChinese('101140101031013') ,dbo.ConvertNumberToChinese('100000001000003') ,dbo.ConvertNumberToChinese('10011003') ,dbo.ConvertNumberToChinese('10010103') ,dbo.ConvertNumberToChinese('10010013') */ end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
set @INTYPE ='' set @NAME ='' set @MEMBERCODE='' set @SEX ='' set @JTDZ ='' set @NL ='' --set @JZYY ='李市中心卫生院' SELECT @JZYY=HOSPITALNA FROM NETCLIENTREG set @YYJB ='' set @RYZD ='' set @DAYS ='' set @STARTEND ='' set @YJBCJE ='' set @FSYLFJE ='' set @ZFYP ='' set @ZFFL ='' set @ZFTJ ='' set @ZFPB ='' set @ZFCL ='' set @ZFQT ='' set @ZFXJ ='' set @FHBXJE ='' set @STARTMONEY='' set @BCBL ='' set @YBCJE ='' set @SBCJE ='' set @JBR ='' set @FHR ='' set @LKR ='' set @SHGBSHR ='' set @SHJE ='' set @BLH ='' SET @ZFJE =''
SELECT @YYJB=C.DESCRIPTION FROM HOSPITALINFOR A,NETCLIENTREG B,Enumeration C WHERE A.HOSPITALID=B.HOSPITALID AND A.YLJGDJID=C.enumId IF ISNULL(@YYJB,'')='' SET @YYJB='未知' select @INTYPE=( case when inType like 'ZYMZBXFF_TYPE_1%' then '门诊' else case when inType like 'ZYMZBXFF_TYPE_2%' then '住院' else '' end end ), @NAME=hzname, @MEMBERCODE=membercode, @SEX=sex, @JTDZ=familydz, @NL=nl, @RYZD=ryzdjbname , @JBR=bb.userloginname, @DAYS=(CASE WHEN DATEDIFF(day,aa.INDATE,aa.OUTDATE)=0 THEN 1 ELSE DATEDIFF(day,aa.INDATE,aa.OUTDATE) END), @STARTEND=(CONVERT(char(10),aa.INDATE, 121) + '到' +CONVERT(char(10),aa.OUTDATE, 121)), @BLH =aa.BLH from cjzdjinfor aa left join userlogin bb on aa.jbrname=bb.userloginid where jzdjlsh=@JZDJLSH and disuse<>'Y' and isnull(jsbz,'')<>'' AND inType like 'ZYMZBXFF_TYPE_2%'IF @INTYPE <>'住院' returnselect @FSYLFJE=sum(jzfsaccount) , @FHBXJE=sum(allaccount) , @YBCJE=sum(jzfsaccount -cash) , @SBCJE=sum(jzfsaccount -cash), @YEAR=YEAR(MAX(JSDATE)), @MONTH=MONTH(MAX(JSDATE)), @DAY=DAY(MAX(JSDATE)), @ZFJE =sum(CASH) from cjzjsinfor where jzdjlsh=@JZDJLSH and disabled<>'Y' SET @datavalue= '[INTYPE:' + @INTYPE + ']' +'[NAME:' + @NAME + ']' +'[MEMBERCODE:' + @MEMBERCODE + ']' +'[SEX:' + @SEX + ']' +'[JTDZ:' + @JTDZ + ']' +'[NL:' + @NL + ']' +'[JZYY:' + @JZYY + ']' +'[YYJB:' + @YYJB + ']' +'[RYZD:' + @RYZD + ']' +'[DAYS:' + @DAYS + ']' +'[STARTEND:' + @STARTEND + ']' +'[YJBCJE:' + @YJBCJE + ']' +'[FSYLFJE:' + @FSYLFJE + ']' +'[ZFYP:' + @ZFYP + ']' +'[ZFFL:' + @ZFFL + ']' +'[ZFTJ:' + @ZFTJ + ']' +'[ZFPB:' + @ZFPB + ']' +'[ZFCL:' + @ZFCL + ']' +'[ZFQT:' + @ZFQT + ']' +'[ZFXJ:' + @ZFXJ + ']' +'[FHBXJE:' + @FHBXJE + ']' +'[STARTMONEY:' + @STARTMONEY + ']' +'[BCBL:' + @BCBL + ']' +'[YBCJE:' + @YBCJE + ']' +'[SBCJE:' + @SBCJE + ']' +'[JBR:' + @JBR + ']' +'[FHR:' + @FHR + ']' +'[LKR:' + @LKR + ']' +'[SHGBSHR:' + @SHGBSHR + ']' +'[SHJE:' + @SHJE + ']' +'[YEAR:' + @YEAR +']' +'[MONTH:' + @MONTH +']' +'[DAY:' + @DAY +']' +'[BLH:' + @BLH +']' +'[ZFJE:' + @ZFJE +']' INSERT @JSQD_MZ(INTYPE,datavalue) select @INTYPE, @datavalue -------------------------------- RETURN END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO这是我的源码
我要取@FHBXJE=sum(allaccount)值的大写
declare @FHBXJE int declare @FHBXJEStr varchar(2000) set @FHBXJEStr=convert(varchar(2000),@FHBXJE)select dbo.ConvertNumberToChinese(@FHBXJEStr)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JDFUN_JSQD_ZY]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[JDFUN_JSQD_ZY] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ---江津_结算清单_住院.sql ---$Id: JJ0524_结算清单_住院.sql,v 1.2 2005/05/25 09:50:38 wxl Exp $ CREATE FUNCTION JDFUN_JSQD_ZY (@JZDJLSH varchar(50)) RETURNS @JSQD_MZ TABLE ( [INTYPE] [varchar] (50) NULL , --'门诊','住院' [DATAVALUE] [varchar] (5000) NULL ) as BEGIN ----------------- declare @INTYPE varchar(50) , -- @NAME varchar(50) , -- @MEMBERCODE varchar(50) ,-- @SEX varchar(50) ,-- @JTDZ varchar(50) ,-- @NL varchar(50) ,-- @JZYY varchar(50) ,-- @YYJB varchar(50) ,-- @RYZD varchar(50) ,-- @DAYS varchar(50) , @STARTEND varchar(50) , @YJBCJE varchar(50) , @FSYLFJE varchar(50) , ---发生医疗费金额
set @INTYPE ='' set @NAME ='' set @MEMBERCODE='' set @SEX ='' set @JTDZ ='' set @NL ='' --set @JZYY ='李市中心卫生院' SELECT @JZYY=HOSPITALNA FROM NETCLIENTREG set @YYJB ='' set @RYZD ='' set @DAYS ='' set @STARTEND ='' set @YJBCJE ='' set @FSYLFJE ='' set @ZFYP ='' set @ZFFL ='' set @ZFTJ ='' set @ZFPB ='' set @ZFCL ='' set @ZFQT ='' set @ZFXJ ='' set @FHBXJE ='' set @STARTMONEY='' set @BCBL ='' set @YBCJE ='' set @SBCJE ='' set @JBR ='' set @FHR ='' set @LKR ='' set @SHGBSHR ='' set @SHJE ='' set @BLH ='' SET @ZFJE ='' set @DX ='' set @FHBXJEStr=convert(varchar(2000),@a)
SELECT @YYJB=C.DESCRIPTION FROM HOSPITALINFOR A,NETCLIENTREG B,Enumeration C WHERE A.HOSPITALID=B.HOSPITALID AND A.YLJGDJID=C.enumId IF ISNULL(@YYJB,'')='' SET @YYJB='未知' select @INTYPE=( case when inType like 'ZYMZBXFF_TYPE_1%' then '门诊' else case when inType like 'ZYMZBXFF_TYPE_2%' then '住院' else '' end end ), @NAME=hzname, @MEMBERCODE=membercode, @SEX=sex, @JTDZ=familydz, @NL=nl, @RYZD=ryzdjbname , @JBR=bb.userloginname, @DAYS=(CASE WHEN DATEDIFF(day,aa.INDATE,aa.OUTDATE)=0 THEN 1 ELSE DATEDIFF(day,aa.INDATE,aa.OUTDATE) END), @STARTEND=(CONVERT(char(10),aa.INDATE, 121) + '到' +CONVERT(char(10),aa.OUTDATE, 121)), @BLH =aa.BLH from cjzdjinfor aa left join userlogin bb on aa.jbrname=bb.userloginid where jzdjlsh=@JZDJLSH and disuse<>'Y' and isnull(jsbz,'')<>'' AND inType like 'ZYMZBXFF_TYPE_2%'IF @INTYPE <>'住院' returnselect @FSYLFJE=sum(jzfsaccount) , @FHBXJE=sum(allaccount) , @a=sum(allaccount) , @YBCJE=sum(jzfsaccount -cash) , @SBCJE=sum(jzfsaccount -cash), @YEAR=YEAR(MAX(JSDATE)), @MONTH=MONTH(MAX(JSDATE)), @DAY=DAY(MAX(JSDATE)), @ZFJE =sum(CASH),
----------------------------------------------------------------------------------------------------
壹拾伍[万](所影响的行数为 1 行)
----------------------------------------------------------------------------------------------------
壹仟伍佰(所影响的行数为 1 行)
*/
GO
SET ANSI_NULLS ON
GO
create function ConvertNumberToChinese(@ varchar(100))
returns varchar(100)
as
begin
--declare @ varchar(100)
--set @ = '101140101031013'
declare @s varchar(100)
set @s = ''
declare @p integer
set @p = 0
declare @m integer
set @m = len(@) % 4
declare @k integer
if @m > 0
begin
set @k = len(@)/4 + 1
end
else
begin
set @k = len(@)/4
end
declare @i integer
set @i = @k
while (@i > 0)
begin --out
declare @L integer
set @L = 4
if @i = @k and @m != 0
begin
set @L = @m
end
declare @ss varchar(4)
set @ss = substring(@,@p+1,@L)
declare @ll integer
set @ll = len(@ss)
--inner
declare @j integer
set @j = 0
while (@j < @ll) --inner
begin --inner
declare @n integer
set @n = cast(substring(@ss,@j+1,1) as integer)
declare @num varchar(2)
set @num =( select Num
from
(
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = @n
)
if @n = 0
begin
if @j < @ll - 1
and cast(substring(@ss,(@j+1)+1,1) as integer) > 0
and right(@ss,1) != @num
begin
set @s = @s + @num
end
end
else
begin
set @s = @s + @num + ( select digit
from
(
select 0 as id,'' as digit
union all select 1,'拾'
union all select 2,'佰'
union all select 3,'仟'
) digits
where id = @ll - @j - 1
)
end
set @j = @j + 1 --inner
end --inner
set @p = @p + @L
declare @unit varchar(10)
set @unit = ( select Unit
from
(
select 0 as id,'' as Unit
union all select 1,'[万]'
union all select 2,'[亿]'
union all select 3,'[万亿]'
) Units
where id = @i - 1
)
if @i < @k
begin
if cast(@ss as integer) != 0
begin
set @s = @s + @unit
end
end
else
begin
set @s = @s + @unit
end
set @i = @i - 1 -- out
end --out
return @s
/**//*
--Test:
select dbo.ConvertNumberToChinese('40000000000')
,dbo.ConvertNumberToChinese('40000000001')
,dbo.ConvertNumberToChinese('400000010000')
,dbo.ConvertNumberToChinese('40101031013')
,dbo.ConvertNumberToChinese('101140101031013')
,dbo.ConvertNumberToChinese('100000001000003')
,dbo.ConvertNumberToChinese('10011003')
,dbo.ConvertNumberToChinese('10010103')
,dbo.ConvertNumberToChinese('10010013')
*/
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
select dbo.ConvertNumberToChinese('150000')select dbo.ConvertNumberToChinese('1500')就可以看到结果.
drop function [dbo].[JDFUN_JSQD_ZY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
---江津_结算清单_住院.sql
---$Id: JJ0524_结算清单_住院.sql,v 1.2 2005/05/25 09:50:38 wxl Exp $
CREATE FUNCTION JDFUN_JSQD_ZY
(@JZDJLSH varchar(50))
RETURNS
@JSQD_MZ TABLE
( [INTYPE] [varchar] (50) NULL , --'门诊','住院'
[DATAVALUE] [varchar] (5000) NULL
)
as
BEGIN
-----------------
declare
@INTYPE varchar(50) , --
@NAME varchar(50) , --
@MEMBERCODE varchar(50) ,--
@SEX varchar(50) ,--
@JTDZ varchar(50) ,--
@NL varchar(50) ,--
@JZYY varchar(50) ,--
@YYJB varchar(50) ,--
@RYZD varchar(50) ,--
@DAYS varchar(50) ,
@STARTEND varchar(50) ,
@YJBCJE varchar(50) ,
@FSYLFJE varchar(50) , ---发生医疗费金额
@ZFYP varchar(50) ,
@ZFFL varchar(50) ,
@ZFTJ varchar(50) ,
@ZFPB varchar(50) ,
@ZFCL varchar(50) ,
@ZFQT varchar(50) ,
@ZFXJ varchar(50) ,
@FHBXJE varchar(50) ,--符合报销金额
@STARTMONEY varchar(50) ,--起报金额
@BCBL varchar(50) ,--补偿比例
@YBCJE varchar(50), --应补偿金额
@SBCJE varchar(50), -- 实补偿金额
@JBR varchar(50) ,-- 经办人
@FHR varchar(50) ,
@LKR varchar(50) ,
@SHGBSHR varchar(50) ,
@SHJE varchar(50) ,
@YEAR varchar(50) ,
@MONTH varchar(50) ,
@DAY varchar(50) ,
@datavalue varchar(5000),
@BLH varchar(500),
@ZFJE varchar(50)
set @INTYPE =''
set @NAME =''
set @MEMBERCODE=''
set @SEX =''
set @JTDZ =''
set @NL =''
--set @JZYY ='李市中心卫生院'
SELECT @JZYY=HOSPITALNA FROM NETCLIENTREG
set @YYJB =''
set @RYZD =''
set @DAYS =''
set @STARTEND =''
set @YJBCJE =''
set @FSYLFJE =''
set @ZFYP =''
set @ZFFL =''
set @ZFTJ =''
set @ZFPB =''
set @ZFCL =''
set @ZFQT =''
set @ZFXJ =''
set @FHBXJE =''
set @STARTMONEY=''
set @BCBL =''
set @YBCJE =''
set @SBCJE =''
set @JBR =''
set @FHR =''
set @LKR =''
set @SHGBSHR =''
set @SHJE =''
set @BLH =''
SET @ZFJE =''
SELECT @YYJB=C.DESCRIPTION FROM HOSPITALINFOR A,NETCLIENTREG B,Enumeration C
WHERE A.HOSPITALID=B.HOSPITALID
AND A.YLJGDJID=C.enumId
IF ISNULL(@YYJB,'')='' SET @YYJB='未知'
select @INTYPE=(
case when inType like 'ZYMZBXFF_TYPE_1%' then '门诊' else
case when inType like 'ZYMZBXFF_TYPE_2%' then '住院' else ''
end end ),
@NAME=hzname,
@MEMBERCODE=membercode,
@SEX=sex,
@JTDZ=familydz,
@NL=nl,
@RYZD=ryzdjbname ,
@JBR=bb.userloginname,
@DAYS=(CASE WHEN DATEDIFF(day,aa.INDATE,aa.OUTDATE)=0 THEN 1 ELSE DATEDIFF(day,aa.INDATE,aa.OUTDATE) END),
@STARTEND=(CONVERT(char(10),aa.INDATE, 121) + '到' +CONVERT(char(10),aa.OUTDATE, 121)),
@BLH =aa.BLH
from cjzdjinfor aa left join userlogin bb on aa.jbrname=bb.userloginid
where jzdjlsh=@JZDJLSH and disuse<>'Y' and isnull(jsbz,'')<>'' AND inType like 'ZYMZBXFF_TYPE_2%'IF @INTYPE <>'住院' returnselect @FSYLFJE=sum(jzfsaccount) ,
@FHBXJE=sum(allaccount) ,
@YBCJE=sum(jzfsaccount -cash) ,
@SBCJE=sum(jzfsaccount -cash),
@YEAR=YEAR(MAX(JSDATE)),
@MONTH=MONTH(MAX(JSDATE)),
@DAY=DAY(MAX(JSDATE)),
@ZFJE =sum(CASH)
from cjzjsinfor where jzdjlsh=@JZDJLSH and disabled<>'Y' SET @datavalue= '[INTYPE:' + @INTYPE + ']'
+'[NAME:' + @NAME + ']'
+'[MEMBERCODE:' + @MEMBERCODE + ']'
+'[SEX:' + @SEX + ']'
+'[JTDZ:' + @JTDZ + ']'
+'[NL:' + @NL + ']'
+'[JZYY:' + @JZYY + ']'
+'[YYJB:' + @YYJB + ']'
+'[RYZD:' + @RYZD + ']'
+'[DAYS:' + @DAYS + ']'
+'[STARTEND:' + @STARTEND + ']'
+'[YJBCJE:' + @YJBCJE + ']'
+'[FSYLFJE:' + @FSYLFJE + ']'
+'[ZFYP:' + @ZFYP + ']'
+'[ZFFL:' + @ZFFL + ']'
+'[ZFTJ:' + @ZFTJ + ']'
+'[ZFPB:' + @ZFPB + ']'
+'[ZFCL:' + @ZFCL + ']'
+'[ZFQT:' + @ZFQT + ']'
+'[ZFXJ:' + @ZFXJ + ']'
+'[FHBXJE:' + @FHBXJE + ']'
+'[STARTMONEY:' + @STARTMONEY + ']'
+'[BCBL:' + @BCBL + ']'
+'[YBCJE:' + @YBCJE + ']'
+'[SBCJE:' + @SBCJE + ']'
+'[JBR:' + @JBR + ']'
+'[FHR:' + @FHR + ']'
+'[LKR:' + @LKR + ']'
+'[SHGBSHR:' + @SHGBSHR + ']'
+'[SHJE:' + @SHJE + ']'
+'[YEAR:' + @YEAR +']'
+'[MONTH:' + @MONTH +']'
+'[DAY:' + @DAY +']'
+'[BLH:' + @BLH +']'
+'[ZFJE:' + @ZFJE +']'
INSERT @JSQD_MZ(INTYPE,datavalue) select @INTYPE, @datavalue
--------------------------------
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO这是我的源码
declare @FHBXJEStr varchar(2000)
set @FHBXJEStr=convert(varchar(2000),@FHBXJE)select dbo.ConvertNumberToChinese(@FHBXJEStr)
drop function [dbo].[JDFUN_JSQD_ZY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
---江津_结算清单_住院.sql
---$Id: JJ0524_结算清单_住院.sql,v 1.2 2005/05/25 09:50:38 wxl Exp $
CREATE FUNCTION JDFUN_JSQD_ZY
(@JZDJLSH varchar(50))
RETURNS
@JSQD_MZ TABLE
( [INTYPE] [varchar] (50) NULL , --'门诊','住院'
[DATAVALUE] [varchar] (5000) NULL
)
as
BEGIN
-----------------
declare
@INTYPE varchar(50) , --
@NAME varchar(50) , --
@MEMBERCODE varchar(50) ,--
@SEX varchar(50) ,--
@JTDZ varchar(50) ,--
@NL varchar(50) ,--
@JZYY varchar(50) ,--
@YYJB varchar(50) ,--
@RYZD varchar(50) ,--
@DAYS varchar(50) ,
@STARTEND varchar(50) ,
@YJBCJE varchar(50) ,
@FSYLFJE varchar(50) , ---发生医疗费金额
@ZFYP varchar(50) ,
@ZFFL varchar(50) ,
@ZFTJ varchar(50) ,
@ZFPB varchar(50) ,
@ZFCL varchar(50) ,
@ZFQT varchar(50) ,
@ZFXJ varchar(50) ,
@FHBXJE varchar(50) ,--符合报销金额
@STARTMONEY varchar(50) ,--起报金额
@BCBL varchar(50) ,--补偿比例
@YBCJE varchar(50), --应补偿金额
@SBCJE varchar(50), -- 实补偿金额
@JBR varchar(50) ,-- 经办人
@FHR varchar(50) ,
@LKR varchar(50) ,
@SHGBSHR varchar(50) ,
@SHJE varchar(50) ,
@YEAR varchar(50) ,
@MONTH varchar(50) ,
@DAY varchar(50) ,
@datavalue varchar(5000),
@BLH varchar(500),
@ZFJE varchar(50),
@DX varchar(100),
@a int,
@FHBXJEStr varchar(2000)
set @INTYPE =''
set @NAME =''
set @MEMBERCODE=''
set @SEX =''
set @JTDZ =''
set @NL =''
--set @JZYY ='李市中心卫生院'
SELECT @JZYY=HOSPITALNA FROM NETCLIENTREG
set @YYJB =''
set @RYZD =''
set @DAYS =''
set @STARTEND =''
set @YJBCJE =''
set @FSYLFJE =''
set @ZFYP =''
set @ZFFL =''
set @ZFTJ =''
set @ZFPB =''
set @ZFCL =''
set @ZFQT =''
set @ZFXJ =''
set @FHBXJE =''
set @STARTMONEY=''
set @BCBL =''
set @YBCJE =''
set @SBCJE =''
set @JBR =''
set @FHR =''
set @LKR =''
set @SHGBSHR =''
set @SHJE =''
set @BLH =''
SET @ZFJE =''
set @DX =''
set @FHBXJEStr=convert(varchar(2000),@a)
SELECT @YYJB=C.DESCRIPTION FROM HOSPITALINFOR A,NETCLIENTREG B,Enumeration C
WHERE A.HOSPITALID=B.HOSPITALID
AND A.YLJGDJID=C.enumId
IF ISNULL(@YYJB,'')='' SET @YYJB='未知'
select @INTYPE=(
case when inType like 'ZYMZBXFF_TYPE_1%' then '门诊' else
case when inType like 'ZYMZBXFF_TYPE_2%' then '住院' else ''
end end ),
@NAME=hzname,
@MEMBERCODE=membercode,
@SEX=sex,
@JTDZ=familydz,
@NL=nl,
@RYZD=ryzdjbname ,
@JBR=bb.userloginname,
@DAYS=(CASE WHEN DATEDIFF(day,aa.INDATE,aa.OUTDATE)=0 THEN 1 ELSE DATEDIFF(day,aa.INDATE,aa.OUTDATE) END),
@STARTEND=(CONVERT(char(10),aa.INDATE, 121) + '到' +CONVERT(char(10),aa.OUTDATE, 121)),
@BLH =aa.BLH
from cjzdjinfor aa left join userlogin bb on aa.jbrname=bb.userloginid
where jzdjlsh=@JZDJLSH and disuse<>'Y' and isnull(jsbz,'')<>'' AND inType like 'ZYMZBXFF_TYPE_2%'IF @INTYPE <>'住院' returnselect @FSYLFJE=sum(jzfsaccount) ,
@FHBXJE=sum(allaccount) ,
@a=sum(allaccount) ,
@YBCJE=sum(jzfsaccount -cash) ,
@SBCJE=sum(jzfsaccount -cash),
@YEAR=YEAR(MAX(JSDATE)),
@MONTH=MONTH(MAX(JSDATE)),
@DAY=DAY(MAX(JSDATE)),
@ZFJE =sum(CASH),
@DX= DBO.ConvertNumberToChinese(@FHBXJEStr)
from cjzjsinfor where jzdjlsh=@JZDJLSH and disabled<>'Y' SET @datavalue= '[INTYPE:' + @INTYPE + ']'
+'[NAME:' + @NAME + ']'
+'[MEMBERCODE:' + @MEMBERCODE + ']'
+'[SEX:' + @SEX + ']'
+'[JTDZ:' + @JTDZ + ']'
+'[NL:' + @NL + ']'
+'[JZYY:' + @JZYY + ']'
+'[YYJB:' + @YYJB + ']'
+'[RYZD:' + @RYZD + ']'
+'[DAYS:' + @DAYS + ']'
+'[STARTEND:' + @STARTEND + ']'
+'[YJBCJE:' + @YJBCJE + ']'
+'[FSYLFJE:' + @FSYLFJE + ']'
+'[ZFYP:' + @ZFYP + ']'
+'[ZFFL:' + @ZFFL + ']'
+'[ZFTJ:' + @ZFTJ + ']'
+'[ZFPB:' + @ZFPB + ']'
+'[ZFCL:' + @ZFCL + ']'
+'[ZFQT:' + @ZFQT + ']'
+'[ZFXJ:' + @ZFXJ + ']'
+'[FHBXJE:' + @FHBXJE + ']'
+'[STARTMONEY:' + @STARTMONEY + ']'
+'[BCBL:' + @BCBL + ']'
+'[YBCJE:' + @YBCJE + ']'
+'[SBCJE:' + @SBCJE + ']'
+'[JBR:' + @JBR + ']'
+'[FHR:' + @FHR + ']'
+'[LKR:' + @LKR + ']'
+'[SHGBSHR:' + @SHGBSHR + ']'
+'[SHJE:' + @SHJE + ']'
+'[YEAR:' + @YEAR +']'
+'[MONTH:' + @MONTH +']'
+'[DAY:' + @DAY +']'
+'[BLH:' + @BLH +']'
+'[ZFJE:' + @ZFJE +']'
+'[DX:' + @DX +']'
INSERT @JSQD_MZ(INTYPE,datavalue) select @INTYPE, @datavalue
--------------------------------
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
http://blog.csdn.net/pbsql/archive/2004/10/29/158666.aspx