sql 取格式结算单数据

sonyet

　　－－存储
　　USE [JSD]
　　GO
　　/****** Object:  StoredProcedure [dbo].[UPDATEJSDMX]    Script Date: 03/12/2015 09:38:37 ******/
　　SET ANSI_NULLS ON
　　GO
　　SET QUOTED_IDENTIFIER ON
　　GO
　　ALTER Procedure [dbo].[UPDATEJSDMX]
　　@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([JSKKMC]) from kk group by[JSKKMC]
　　exec('select [JLBH],[WLDW],'+@s+',[HJKK] into KK_N from (select *,[HJKK]=sum([JSKKJE]) over(partition by [JLBH],[WLDW]) from kk) a pivot (max([JSKKJE]) for [JSKKMC] 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
　　from  KL_N L,KK_N K,KP P
　　where L.JLBH=K.JLBH
　　and L.JLBH=P.JLBH
　　end
　　--程序调用
　　exec updatejsdmx 201501
　　－－使用到FUNCTION
　　USE [JSD]
　　GO
　　/****** Object:  UserDefinedFunction [dbo].[split]    Script Date: 03/12/2015 15:56:50 ******/
　　SET ANSI_NULLS ON
　　GO
　　SET QUOTED_IDENTIFIER ON
　　GO
　　ALTER function [dbo].[split](
　　@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 [JSD]
　　GO
　　/****** Object:  UserDefinedFunction [dbo].[L2U]    Script Date: 03/12/2015 15:57:12 ******/
　　SET ANSI_NULLS ON
　　GO
　　SET QUOTED_IDENTIFIER ON
　　GO
　　ALTER FUNCTION [dbo].[L2U](@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