sql 取格式结算单数据
－－存储USE
GO
/****** Object:StoredProcedure . Script Date: 03/12/2015 09:38:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure .
@YEARMONTH int
as
begin
declare @sql varchar(1000)
declare @YEARMONTH1 varchar(20)
declare @s nvarchar(4000)
set @YEARMONTH1=CAST(@YEARMONTH as Varchar(20))
if exists (select * from sysobjects where> drop table KL
set @sql='select J.JLBH,substring(I.DEPTID,1,4) DEPTID,convert(char,getdate(),102) RQ'+
+',W.NAME WLDW,S.NAME SPSB,I.JSKL,sum(I.XSJE+I.YHJE) XSJE '+
+' from JXC.BFBHDD.JSD J,JXC.BFBHDD.JSDITEM I,JXC.BFBHDD.WLDW W,JXC.BFBHDD.SPSB S,JXC.BFBHDD.SPXX X'+
+' where J.JLBH=I.JLBH'+
+' and J.YEARMONTH='+@YEARMONTH1
+' and J.WLDW=W.CODE'+
+' and I.SP_ID=X.SP_ID'+
+' and X.SB=S.SBID'+
+' and I.JSKL>=0'+
+' group by J.JLBH,substring(I.DEPTID,1,4),W.NAME,S.NAME,I.JSKL'
set @sql='select * into KL from openquery(MSybase,'''+@sql+''')'
--print(@sql)
exec(@sql)
if exists (select * from sysobjects where> drop table KK
set @sql='select J.JLBH,W.NAME WLDW,I.JSKKMC,I.JSKKJE'+
+' from JXC.BFBHDD.JSD J,JXC.BFBHDD.JSDKKITEM I,JXC.BFBHDD.WLDW W'+
+' where J.JLBH=I.JLBH'+
+' and J.YEARMONTH='+@YEARMONTH1
+' and J.WLDW=W.CODE'+
+' group by J.JLBH,W.NAME,I.JSKKMC,I.JSKKJE'
set @sql='select * into KK from openquery(MSybase,'''+@sql+''')'
exec(@sql)
if exists (select * from sysobjects where> drop table KP
set @sql='select JLBH,sum(JSJE_17+ZZSJE_17+JSJE_QT) KPJE from JXC.BFBHDD.JSD where YEARMONTH='+@YEARMONTH1+' group by JLBH order by JLBH'
set @sql='select * into KP from openquery(MSybase,'''+@sql+''')'
exec(@sql)
if exists (select * from sysobjects where> drop table KL_N
select JLBH,DEPTID,WLDW,SPSB,KL=(stuff((select ';'+convert(varchar,JSKL)+ ';' + convert(varchar,XSJE) from KL where JLBH=a.JLBH for xml path('')),1,1,'')),HZJE=(select sum(XSJE) from KL where JLBH=a.JLBH) into KL_N from KL a group by JLBH,DEPTID,WLDW,SPSB
if exists (select * from sysobjects where> drop table KK_N
select @s=isnull(@s+',','')+quotename() from kk group by
exec('select ,,'+@s+', into KK_N from (select *,=sum() over(partition by ,) from kk) a pivot (max() for in('+@s+'))b ')
if exists (select * from sysobjects where> drop table JSD
select l.deptid,l.spsb,
dbo.split(l.KL,';',1) as kl1, dbo.split(l.kl,';',2) as xs1,
dbo.split(l.kl,';',3) as kl2,dbo.split(l.kl,';',4) as XS2,
dbo.split(l.kl,';',5) as kl3,dbo.split(l.kl,';',6) as XS3,
dbo.split(l.kl,';',7) as kl4,dbo.split(l.kl,';',8) as XS4,
dbo.split(l.kl,';',9) as kl5,dbo.split(l.kl,';',10) as XS5,
dbo.split(l.kl,';',11) as kl6,dbo.split(l.kl,';',12) as XS6,
dbo.split(l.kl,';',13) as kl7,dbo.split(l.kl,';',14) as XS7,
l.HZJE,K.*,P.KPJE,P.KPJE-K.HJKK SJJE,HZJE-KPJE MLE,(HZJE-KPJE)/HZJE MLL,dbo.L2U(P.KPJE-K.HJKK,1) RMB into JSD
fromKL_N L,KK_N K,KP P
where L.JLBH=K.JLBH
and L.JLBH=P.JLBH
end
--程序调用
exec updatejsdmx 201501
－－使用到FUNCTION
USE
GO
/****** Object:UserDefinedFunction . Script Date: 03/12/2015 15:56:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function .(
@srcstr varchar(4000),
@space varchar(50),
@showindex int
)returns varchar(4000)
begin
declare @pos1 int
declare @pos2 int
declare @index int
declare @return varchar(4000)
set @pos1 = 0
set @pos2 = 0
set @index = 0
set @srcstr = @srcstr + @space
while @index0
set @return = substring(@srcstr,@pos1+1,@pos2-@pos1-1)
else
begin
set @pos2 = len(@srcstr)
set @return = ''
end
set @index = @index + 1
end
return(@return)
end
----------------------------------
USE
GO
/****** Object:UserDefinedFunction . Script Date: 03/12/2015 15:57:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION .(@n_LowerMoney numeric(15,2),@v_TransType int)
RETURNS VARCHAR(200) AS
BEGIN
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int
set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格
set @i_I = 1
set @v_UpperStr = ''
while ( @i_I
页:
[1]