--1----创建数据库对象_表 CREATE TABLE [仓库表] ( [仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL , [仓库名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK_仓库表] PRIMARY KEY CLUSTERED ( [仓库代码] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [商品表] ( [商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL , [商品名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK_商品表] PRIMARY KEY CLUSTERED ( [商品代码] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [库存表] ( [仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL , [商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL , [年度] [int] NOT NULL , [月份] [int] NOT NULL , [期初库存数量] [int] NOT NULL CONSTRAINT [DF_库存表_库存数量] DEFAULT (0), [期末库存数量] [int] NOT NULL CONSTRAINT [DF_库存表_期末库存数量] DEFAULT (0), CONSTRAINT [PK_库存表] PRIMARY KEY CLUSTERED ( [仓库代码], [商品代码], [年度], [月份] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [进货表] ( [进货单号] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL , [商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , [进货数量] [int] NULL , [进货日期] [datetime] NULL , [仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK_进货表] PRIMARY KEY CLUSTERED ( [进货单号] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [销售表] ( [销售单号] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL , [商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , [出货数量] [int] NULL , [出货日期] [datetime] NULL , [仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK_销售表] PRIMARY KEY CLUSTERED ( [销售单号] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [转仓表] ( [转仓单号] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL , [商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , [转仓数量] [int] NULL , [转仓日期] [datetime] NULL , [仓库代码_转出] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , [仓库代码_转入] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK_转仓表] PRIMARY KEY CLUSTERED ( [转仓单号] ) ON [PRIMARY] ) ON [PRIMARY] GOCREATE TABLE [商品进销转存日报表] ( [仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL , [商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL , [年度] [int] NOT NULL , [月份] [int] NOT NULL , [日期] [int] NOT NULL , [期初余额] [int] NOT NULL , [进货] [int] NOT NULL , [销售] [int] NOT NULL , [转入] [int] NOT NULL , [转出] [int] NOT NULL , [期末余额] [int] NOT NULL , CONSTRAINT [PK_商品进销转存日报表] PRIMARY KEY CLUSTERED ( [仓库代码], [商品代码], [年度], [月份], [日期] ) ON [PRIMARY] ) ON [PRIMARY] GO
----2--------- /* 存储过程 名称:商品进销转存日报表数据 输入参数: @CKDM 仓库代码 @Year 年度 @Month 月份 输出参数: 调用范例: EXEC SP_Get_SPJXZCRBB C01,2005,11 作者:[email protected] 日期:2005年12月5日 04:14 */ CREATE PROCEDURE [dbo].[SP_Get_SPJXZCRBB] @CKDM varchar(3),@Year int,@Month int AS BEGIN -------------------------------------------------------------------------------- --1、每月在“月结”时,应(1)生成所有仓库所有商品的本月统计报表;(2)重置本月末库存数;(3)生成下月初库存数 -------------------------------------------------------------------------------- --(1)生成所有仓库所有商品的指定年月的每日统计数据 --DECLARE @Year int,@Month int --定义变量:报表年度、报表月份 --SELECT @Year=2005,@Month=11 --赋值:报表年度、报表月份(生成及查询时用) --生成报表(如报表中已有该记录,应先删除后重新生成) DELETE FROM [商品进销转存日报表] WHERE [年度]=@Year AND [月份]=@Month/* --逐仓库统计_开始 DECLARE cur_CKDM CURSOR FOR SELECT [仓库代码] FROM [仓库表] OPEN cur_CKDM DECLARE @CKDM varchar(3) --定义变量:仓库代码 FETCH NEXT FROM cur_CKDM INTO @CKDM WHILE @@FETCH_STATUS = 0 BEGIN */ --按仓库、年月,进行逐商品统计_开始 DECLARE cur_SPDM CURSOR FOR SELECT [商品代码] FROM [商品表] OPEN cur_SPDM --定义变量:商品代码、日期、本日期初库存余额、本日期末库存余额、本日进货数、本日销售数、本日转仓_入数、本日转仓_出数、 DECLARE @SPDM varchar(3),@Day int,@QC int,@QM int,@J int,@X int,@ZR int,@ZC int FETCH NEXT FROM cur_SPDM INTO @SPDM WHILE @@FETCH_STATUS = 0 BEGIN --逐日统计_开始 SET @Day=1 --当年度月份,1~28/29/30/31日循环统计(有的月份无29/30/31日) WHILE @Day<=DATEDIFF(DAY,CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month)+'-1',CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month+1)+'-1') BEGIN IF @Day=1 SELECT @QC=ISNULL([期初库存数量],0) FROM [库存表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month ELSE SELECT @QC=@QM SELECT @J =ISNULL(SUM([进货数量]),0) FROM [进货表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND YEAR([进货日期])=@Year AND MONTH([进货日期])=@Month AND DAY([进货日期])=@Day SELECT @X =ISNULL(SUM([出货数量]),0) FROM [销售表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND YEAR([出货日期])=@Year AND MONTH([出货日期])=@Month AND DAY([出货日期])=@Day SELECT @ZR=ISNULL(SUM([转仓数量]),0) FROM [转仓表] WHERE [仓库代码_转入]=@CKDM AND [商品代码]=@SPDM AND YEAR([转仓日期])=@Year AND MONTH([转仓日期])=@Month AND DAY([转仓日期])=@Day SELECT @ZC=ISNULL(SUM([转仓数量]),0) FROM [转仓表] WHERE [仓库代码_转出]=@CKDM AND [商品代码]=@SPDM AND YEAR([转仓日期])=@Year AND MONTH([转仓日期])=@Month AND DAY([转仓日期])=@Day SELECT @QM=@QC+@J-@X+@ZR-@ZC INSERT INTO [商品进销转存日报表]([仓库代码],[商品代码],[年度],[月份],[日期],[期初余额],[进货],[销售],[转入],[转出],[期末余额]) VALUES (@CKDM,@SPDM,@Year,@Month,@Day,@QC,@J,@X,@ZR,@ZC,@QM) SET @Day=@Day+1 END --逐日统计_结束 FETCH NEXT FROM cur_SPDM INTO @SPDM END CLOSE cur_SPDM DEALLOCATE cur_SPDM --按仓库、年月,进行逐商品统计_结束/* FETCH NEXT FROM cur_CKDM INTO @CKDM END CLOSE cur_CKDM DEALLOCATE cur_CKDM --逐仓库统计_结束*/ --SELECT * FROM [商品进销转存日报表]----------------------------------------------------------------- --(2)重置该年该月的[库存表].[期末库存数量] UPDATE [库存表] SET [期末库存数量]=R.[期末余额] FROM [商品进销转存日报表] R WHERE [库存表].[仓库代码]=R.[仓库代码] AND [库存表].[商品代码]=R.[商品代码] AND [库存表].[年度]=@Year AND [库存表].[月份]=@Month AND R.[年度]=@Year AND R.[月份]=@Month AND R.[日期]=DATEDIFF(DAY,CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month)+'-1',CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month+1)+'-1') ----------------------------------------------------------------- --(3)生成该年该月的下一个月的[库存表].[期初库存数量] DELETE FROM [库存表] WHERE [年度]=CASE @Month WHEN 12 THEN @Year+1 ELSE @Year END AND [月份]=CASE @Month WHEN 12 THEN 1 ELSE @Month+1 END INSERT INTO [库存表] ([仓库代码], [商品代码], [年度], [月份], [期初库存数量], [期末库存数量]) SELECT [仓库代码],[商品代码],CASE @Month WHEN 12 THEN @Year+1 ELSE @Year END AS [年度], CASE @Month WHEN 12 THEN 1 ELSE @Month+1 END AS [月份], [期末库存数量] AS [期初库存数量],0 AS [期末库存数量] FROM [库存表] WHERE [年度]=@Year AND [月份]=@Month -------------------------------------------------------------------------------- ENDGO
--------3-A----------------- /* 用户自定义函数 名称:商品进销转存日报表_月 输入参数: @CKDM 仓库代码 @Year 年度 @Month 月份 输出参数:返回表 调用范例:IF (SELECT TOP 1 [D1] FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)) IS NULL BEGIN --如果报表记录不存在,则先用存储过程生成 EXEC SP_Get_SPJXZCRBB C01,2005,11 SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,11) END ELSE --如果报表记录存在,则直接提取 SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)注意:[D29]~[D31]列的动态绑定 作者:[email protected] 日期:2005年12月5日 04:14 */ CREATE FUNCTION FN_Get_SPJXZCRBB_Month (@CKDM varchar(3),@Year int,@Month int) RETURNS @Report TABLE ( [SPDM] varchar(3), [序号] int, [项目] varchar(20), [D1] int, [D2] int, [D3] int, [D4] int, [D5] int, [D6] int, [D7] int, [D8] int, [D9] int, [D10] int, [D11] int, [D12] int, [D13] int, [D14] int, [D15] int, [D16] int, [D17] int, [D18] int, [D19] int, [D20] int, [D21] int, [D22] int, [D23] int, [D24] int, [D25] int, [D26] int, [D27] int, [D28] int, [D29] int, [D30] int, [D31] int) AS BEGIN --逐商品统计_开始 DECLARE cur_SPDM CURSOR FOR SELECT [商品代码] FROM [商品表] OPEN cur_SPDM DECLARE @SPDM varchar(3),@I int FETCH NEXT FROM cur_SPDM INTO @SPDM WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @Report SELECT @SPDM,1,'期初库存',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL UNION SELECT @SPDM,2,'进',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL UNION SELECT @SPDM,3,'销',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL UNION SELECT @SPDM,4,'转(+入-出)',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL UNION SELECT @SPDM,5,'期末库存',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL SET @I=1 WHILE @I<=5 BEGIN UPDATE @Report SET [D1]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=1) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D2]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=2) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D3]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=3) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D4]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=4) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D5]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=5) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D6]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=6) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D7]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=7) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D8]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=8) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D9]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=9) WHERE [SPDM]=@SPDM AND [序号]=@I
--------3-B----------------- UPDATE @Report SET [D10]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=10) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D11]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=11) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D12]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=12) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D13]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=13) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D14]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=14) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D15]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=15) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D16]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=16) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D17]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=17) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D18]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=18) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D19]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=19) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D20]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=20) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D21]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=21) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D22]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=22) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D23]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=23) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D24]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=24) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D25]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=25) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D26]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=26) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D27]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=27) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D28]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=28) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D29]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=29) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D30]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=30) WHERE [SPDM]=@SPDM AND [序号]=@I UPDATE @Report SET [D31]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=31) WHERE [SPDM]=@SPDM AND [序号]=@I SET @I=@I+1 END -------------------------------------- FETCH NEXT FROM cur_SPDM INTO @SPDM END CLOSE cur_SPDM DEALLOCATE cur_SPDM --逐商品统计_结束 RETURN END SET @I=@I+1 END -------------------------------------- FETCH NEXT FROM cur_SPDM INTO @SPDM END CLOSE cur_SPDM DEALLOCATE cur_SPDM --逐商品统计_结束 RETURN END ------------------看函数的调用范例即可------------------ (示例下载:http://www.anyitimes.com.cn/data/JXC_Sample.rar)
SET @I=@I+1 END -------------------------------------- FETCH NEXT FROM cur_SPDM INTO @SPDM END CLOSE cur_SPDM DEALLOCATE cur_SPDM --逐商品统计_结束 RETURN END----------------------------------------- 上面这一段粘重复了,自己删掉; 你的转仓表设计有问题; 提什么钱啊,你TMD真俗。 -----------------------------------------
--建立测试环境 create table #dm_wz(dm int identity(1,1),wz_name varchar(10)) insert into #dm_wz(wz_name) select 'abs' union all select 'bbs' union all select 'tvb' create table #dm_ck(dm int identity(1,1),ck_name varchar(10)) insert into #dm_ck(ck_name) select 'wl' union all select 'ql' union all select 'fly' create table #insl(dm int identity(1,1),in_sl int, in_date datetime,ckdm int) insert into #insl(in_sl,in_date,ckdm) select 20,'2005-08-02 00:08:00',2 union all select 56,'2005-08-05 06:28:00',3 union all select 87,'2005-08-07 12:38:40',1create table #out(dm int identity(1,1),o_sl int, o_date datetime,ckdm int) insert into #out(o_sl,o_date,ckdm) select 25,'2005-08-06 00:08:00',2 union all select 33,'2005-08-02 06:28:00',3 union all select 57,'2005-08-04 12:38:40',1create table #zc(dm int identity(1,1),zc_sl int, zc_date datetime,ckdm int) insert into #zc(zc_sl,zc_date,ckdm) select 22,'2005-08-03 00:08:00',2 union all select 4,'2005-08-08 06:28:00',3 union all select 52,'2005-08-02 12:38:40',1create table #cun(dm int identity(1,1),c_sl int, ckdm int) insert into #cun(c_sl,ckdm) select 89,3 union all select 34,2 union all select 190,1 ----------------------------------- select d.dm as 商品代码 ,'进' as 类型, SUM(Case day(in_date) When 1 Then in_sl Else 0 End) As N'1号', SUM(Case day(in_date) When 2 Then in_sl Else 0 End) As N'2号', SUM(Case day(in_date) When 3 Then in_sl Else 0 End) As N'3号', SUM(Case day(in_date) When 4 Then in_sl Else 0 End) As N'4号', SUM(Case day(in_date) When 5 Then in_sl Else 0 End) As N'5号', SUM(Case day(in_date) When 6 Then in_sl Else 0 End) As N'6号', SUM(Case day(in_date) When 7 Then in_sl Else 0 End) As N'7号', SUM(Case day(in_date) When 8 Then in_sl Else 0 End) As N'8号' from #dm_wz d Inner Join #insl i On d.dm=i.dm Group By d.dmunion select d.dm ,'销', SUM(Case day(o_date) When 1 Then o_sl Else 0 End) As N'1号', SUM(Case day(o_date) When 2 Then o_sl Else 0 End) As N'2号', SUM(Case day(o_date) When 3 Then o_sl Else 0 End) As N'3号', SUM(Case day(o_date) When 4 Then o_sl Else 0 End) As N'4号', SUM(Case day(o_date) When 5 Then o_sl Else 0 End) As N'5号', SUM(Case day(o_date) When 6 Then o_sl Else 0 End) As N'6号', SUM(Case day(o_date) When 7 Then o_sl Else 0 End) As N'7号', SUM(Case day(o_date) When 8 Then o_sl Else 0 End) As N'8号' from #dm_wz d Inner Join #out o On d.dm=o.dm Group By d.dm union select d.dm ,'转', SUM(Case day(zc_date) When 1 Then zc_sl Else 0 End) As N'1号', SUM(Case day(zc_date) When 2 Then zc_sl Else 0 End) As N'2号', SUM(Case day(zc_date) When 3 Then zc_sl Else 0 End) As N'3号', SUM(Case day(zc_date) When 4 Then zc_sl Else 0 End) As N'4号', SUM(Case day(zc_date) When 5 Then zc_sl Else 0 End) As N'5号', SUM(Case day(zc_date) When 6 Then zc_sl Else 0 End) As N'6号', SUM(Case day(zc_date) When 7 Then zc_sl Else 0 End) As N'7号', SUM(Case day(zc_date) When 8 Then zc_sl Else 0 End) As N'8号' from #dm_wz d Inner Join #zc z On d.dm=z.dm Group By d.dmdrop table #dm_wz,#dm_ck,#insl,#out,#zc,#cun /* -------------------------------------------------------商品代码 类型 1号 2号 3号 4号 5号 6号 7号 8号 1 存 0 -5 0 0 0 0 0 0 1 进 0 20 0 0 0 0 0 0 1 销 0 0 0 0 0 25 0 0 1 转 0 0 22 0 0 0 0 0 2 存 0 0 0 0 23 0 0 0 2 进 0 0 0 0 56 0 0 0 2 销 0 33 0 0 0 0 0 0 2 转 0 0 0 0 0 0 0 4 3 存 0 0 0 0 0 0 30 0 3 进 0 0 0 0 0 0 87 0 3 销 0 0 0 57 0 0 0 0 3 转 0 52 0 0 0 0 0 0 .............................*/
--1#仓库,2005年11月的报表 SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)--2#仓库,2005年11月的报表 SELECT * FROM FN_Get_SPJXZCRBB_Month('C02',2005,11)--1#仓库,2005年12月的报表 SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,12)--2#仓库,2005年12月的报表 SELECT * FROM FN_Get_SPJXZCRBB_Month('C02',2005,12)--如果跨月份,你就UNION后SUM--GROUP BY 即可。
我在看ranzj(冉冉) 提供的内容,非常详细,非常好,有大家的风范,几乎无可挑剔, 使用了存储过程,自定义函数,游标,变量,种种情况都考虑了,我还没有看懂全部.不可否认,他是一种好方法,但是如果仅要体现进销存的内容,他的方法复杂了点,但他的方法体现了上存和结存,这是只可以查某一天的存储过程:是否可以改进一下为1-31的, CREATE PROCEDURE Total_IOS(@StartDate datetime,@EndDate datetime, @Depot char(20), @Goods char(30) ) AS--参数:时间段,仓库,仓库类别,商品,商品类别declare @StartDate1 datetime,@EndDate1 datetime, @StartDate2 datetime,@EndDate2 datetime declare @SQL varchar(6000),@Where1 varchar(300),@Where2 varchar(300)select @StartDate1=@StartDate,@EndDate1=@EndDate,@StartDate2=DateAdd(day,1,@EndDate),@EndDate2=cast(GetDate() as char)select @Where1='',@Where2='1=1'if @Depot<>'' begin select @Where1='and (dm_ck.dm like ' + char(39) + rtrim(@Depot) + '%' + char(39) + ' or dm_ck.mc like ' + char(39) + '%' + rtrim(@Depot) + '%' + char(39) + ' or dm_ck.lbcode like ' + char(39) + '%' + rtrim(@Depot) + '%' + char(39) + ')' endif @Goods<>'' begin select @Where2=@Where2 + ' and (a.dm like ' + char(39) + rtrim(@Goods) + '%' + char(39) + ' or a.mc like ' + char(39) + '%' + rtrim(@Goods) + '%' + char(39) + ' or a.lbcode like ' + char(39) + '%' + rtrim(@Goods) + '%' + char(39) + ')' endselect @SQL=' select wzdm, wzmc, dj, (cun_sl + o_sl1 + zc_o_sl1 + o_sl2 + zc_o_sl2 - in_sl1 - zc_in_sl1 - in_sl2 - zc_in_sl2) as l_sl, in_sl1 as in_sl, o_sl1 as o_sl, zc_in_sl1 as zc_in_sl, zc_o_sl1 as zc_o_sl, (cun_sl + o_sl2 + zc_o_sl2 - in_sl2 - zc_in_sl2) as c_sl from (select a.dm as wzdm, a.mc as wzmc, a.dj as dj, isnull(b.in_sl,0) as in_sl1 , isnull(c.o_sl,0) as o_sl1, isnull(d.sl1,0) as zc_o_sl1, isnull(e.sl2,0) as zc_in_sl1, isnull(f.in_sl,0) as in_sl2 , isnull(g.o_sl,0) as o_sl2, isnull(h.sl1,0) as zc_o_sl2, isnull(i.sl2,0) as zc_in_sl2, isnull(j.cun_sl,0) as cun_sl from dm_wz a left join (select wzdm, sum(in_sl)as in_sl, sum(in_jine) as in_jine from ins1 inner join dm_ck on ins1.ckdm=dm_ck.dm where DateDiff(day,in_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,in_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + ' group by wzdm) as b on a.dm=b.wzdm left join (select wzdm, sum(o_sl) as o_sl, sum(o_jine) as o_jine from out inner join dm_ck on out.ckdm=dm_ck.dm where DateDiff(day,o_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + ' group by wzdm) as c on a.dm=c.wzdm left join (select wzdm1, sum(sl1) as sl1, sum(jine1) as jine1 from zc inner join dm_ck on zc.ckdm1=dm_ck.dm where DateDiff(day,o_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + ' group by wzdm1) as d on a.dm=d.wzdm1 left join (select wzdm2, sum(sl2) as sl2, sum(jine2) as jine2 from zc inner join dm_ck on zc.ckdm2=dm_ck.dm where DateDiff(day,o_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + ' group by wzdm2) as e on a.dm=e.wzdm2 left join (select wzdm, sum(in_sl)as in_sl, sum(in_jine) as in_jine from ins1 inner join dm_ck on ins1.ckdm=dm_ck.dm where DateDiff(day,in_date,' + char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,in_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + ' group by wzdm) as f on a.dm=f.wzdm left join (select wzdm, sum(o_sl) as o_sl, sum(o_jine) as o_jine from out inner join dm_ck on out.ckdm=dm_ck.dm where DateDiff(day,o_date,'+ char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + ' group by wzdm) as g on a.dm=g.wzdm left join (select wzdm1, sum(sl1) as sl1, sum(jine1) as jine1 from zc inner join dm_ck on zc.ckdm1=dm_ck.dm where DateDiff(day,o_date,'+ char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + ' group by wzdm1) as h on a.dm=h.wzdm1 left join (select wzdm2, sum(sl2) as sl2, sum(jine2) as jine2 from zc inner join dm_ck on zc.ckdm2=dm_ck.dm where DateDiff(day,o_date,'+ char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + ' group by wzdm2) as i on a.dm=i.wzdm2 left join (select wzdm, sum(sl) as cun_sl from cun inner join dm_ck on cun.ckdm=dm_ck.dm where 1=1' + @where1 + ' group by wzdm) as j on a.dm=j.wzdm where ' + @Where2 + ') as tmp'--print @SQLexec(@SQL)
ranzj(冉冉) ,我逐一将原数据库数据导到你的数据表, --insert into 仓库表 select dm,mc from dm_ck --insert into 商品表 select dm,mc from dm_wz //insert into 商品进销转存日报表 select dm,mc from dm_ck //insert into 库存表 select ckdm,wzdm,'2005','11'dm,mc from dm_wz //2个表无对应内容,比如[库存表]无 [年度] [int] NOT NULL , [月份] [int] NOT NULL , [期初库存数量] [int] NOT NULL , [期末库存数量] [int] NOT NULL --insert into 转仓表 select zc_no,wzdm1,sl1,o_date,ckdm1,ckdm2 from zc --insert into 进货表 select in_no,wzdm,in_sl,in_date,ckdm from ins1 --insert into 销售表 select o_no,wzdm,o_sl,o_date,ckdm from out 其他表导入后,执行SELECT * FROM FN_Get_SPJXZCRBB_Month('0001',2005,11) 5分钟数据还没有出来,商品代码有12000条,其他演示数据都只有几十条,速度也是个问题
仓库代码只有1个,不知道是出库库房还是入库库房
提什么钱啊,你TMD真俗
CREATE TABLE [仓库表] (
[仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[仓库名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_仓库表] PRIMARY KEY CLUSTERED
(
[仓库代码]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [商品表] (
[商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[商品名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_商品表] PRIMARY KEY CLUSTERED
(
[商品代码]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [库存表] (
[仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[年度] [int] NOT NULL ,
[月份] [int] NOT NULL ,
[期初库存数量] [int] NOT NULL CONSTRAINT [DF_库存表_库存数量] DEFAULT (0),
[期末库存数量] [int] NOT NULL CONSTRAINT [DF_库存表_期末库存数量] DEFAULT (0),
CONSTRAINT [PK_库存表] PRIMARY KEY CLUSTERED
(
[仓库代码],
[商品代码],
[年度],
[月份]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [进货表] (
[进货单号] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[进货数量] [int] NULL ,
[进货日期] [datetime] NULL ,
[仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_进货表] PRIMARY KEY CLUSTERED
(
[进货单号]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [销售表] (
[销售单号] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[出货数量] [int] NULL ,
[出货日期] [datetime] NULL ,
[仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_销售表] PRIMARY KEY CLUSTERED
(
[销售单号]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [转仓表] (
[转仓单号] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[转仓数量] [int] NULL ,
[转仓日期] [datetime] NULL ,
[仓库代码_转出] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[仓库代码_转入] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_转仓表] PRIMARY KEY CLUSTERED
(
[转仓单号]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [商品进销转存日报表] (
[仓库代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[商品代码] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[年度] [int] NOT NULL ,
[月份] [int] NOT NULL ,
[日期] [int] NOT NULL ,
[期初余额] [int] NOT NULL ,
[进货] [int] NOT NULL ,
[销售] [int] NOT NULL ,
[转入] [int] NOT NULL ,
[转出] [int] NOT NULL ,
[期末余额] [int] NOT NULL ,
CONSTRAINT [PK_商品进销转存日报表] PRIMARY KEY CLUSTERED
(
[仓库代码],
[商品代码],
[年度],
[月份],
[日期]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/*
存储过程
名称:商品进销转存日报表数据
输入参数:
@CKDM 仓库代码
@Year 年度
@Month 月份
输出参数:
调用范例: EXEC SP_Get_SPJXZCRBB C01,2005,11
作者:[email protected]
日期:2005年12月5日 04:14
*/
CREATE PROCEDURE [dbo].[SP_Get_SPJXZCRBB]
@CKDM varchar(3),@Year int,@Month int
AS
BEGIN
--------------------------------------------------------------------------------
--1、每月在“月结”时,应(1)生成所有仓库所有商品的本月统计报表;(2)重置本月末库存数;(3)生成下月初库存数
-------------------------------------------------------------------------------- --(1)生成所有仓库所有商品的指定年月的每日统计数据 --DECLARE @Year int,@Month int --定义变量:报表年度、报表月份
--SELECT @Year=2005,@Month=11 --赋值:报表年度、报表月份(生成及查询时用) --生成报表(如报表中已有该记录,应先删除后重新生成)
DELETE FROM [商品进销转存日报表] WHERE [年度]=@Year AND [月份]=@Month/* --逐仓库统计_开始
DECLARE cur_CKDM CURSOR FOR SELECT [仓库代码] FROM [仓库表]
OPEN cur_CKDM
DECLARE @CKDM varchar(3) --定义变量:仓库代码
FETCH NEXT FROM cur_CKDM INTO @CKDM
WHILE @@FETCH_STATUS = 0
BEGIN */ --按仓库、年月,进行逐商品统计_开始
DECLARE cur_SPDM CURSOR FOR SELECT [商品代码] FROM [商品表]
OPEN cur_SPDM
--定义变量:商品代码、日期、本日期初库存余额、本日期末库存余额、本日进货数、本日销售数、本日转仓_入数、本日转仓_出数、
DECLARE @SPDM varchar(3),@Day int,@QC int,@QM int,@J int,@X int,@ZR int,@ZC int
FETCH NEXT FROM cur_SPDM INTO @SPDM
WHILE @@FETCH_STATUS = 0
BEGIN --逐日统计_开始
SET @Day=1
--当年度月份,1~28/29/30/31日循环统计(有的月份无29/30/31日)
WHILE @Day<=DATEDIFF(DAY,CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month)+'-1',CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month+1)+'-1')
BEGIN
IF @Day=1 SELECT @QC=ISNULL([期初库存数量],0) FROM [库存表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month
ELSE SELECT @QC=@QM
SELECT @J =ISNULL(SUM([进货数量]),0) FROM [进货表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND YEAR([进货日期])=@Year AND MONTH([进货日期])=@Month AND DAY([进货日期])=@Day
SELECT @X =ISNULL(SUM([出货数量]),0) FROM [销售表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND YEAR([出货日期])=@Year AND MONTH([出货日期])=@Month AND DAY([出货日期])=@Day
SELECT @ZR=ISNULL(SUM([转仓数量]),0) FROM [转仓表] WHERE [仓库代码_转入]=@CKDM AND [商品代码]=@SPDM AND YEAR([转仓日期])=@Year AND MONTH([转仓日期])=@Month AND DAY([转仓日期])=@Day
SELECT @ZC=ISNULL(SUM([转仓数量]),0) FROM [转仓表] WHERE [仓库代码_转出]=@CKDM AND [商品代码]=@SPDM AND YEAR([转仓日期])=@Year AND MONTH([转仓日期])=@Month AND DAY([转仓日期])=@Day
SELECT @QM=@QC+@J-@X+@ZR-@ZC INSERT INTO [商品进销转存日报表]([仓库代码],[商品代码],[年度],[月份],[日期],[期初余额],[进货],[销售],[转入],[转出],[期末余额])
VALUES (@CKDM,@SPDM,@Year,@Month,@Day,@QC,@J,@X,@ZR,@ZC,@QM) SET @Day=@Day+1
END
--逐日统计_结束 FETCH NEXT FROM cur_SPDM INTO @SPDM
END
CLOSE cur_SPDM
DEALLOCATE cur_SPDM
--按仓库、年月,进行逐商品统计_结束/* FETCH NEXT FROM cur_CKDM INTO @CKDM
END
CLOSE cur_CKDM
DEALLOCATE cur_CKDM
--逐仓库统计_结束*/ --SELECT * FROM [商品进销转存日报表]-----------------------------------------------------------------
--(2)重置该年该月的[库存表].[期末库存数量]
UPDATE [库存表] SET [期末库存数量]=R.[期末余额] FROM [商品进销转存日报表] R
WHERE [库存表].[仓库代码]=R.[仓库代码] AND [库存表].[商品代码]=R.[商品代码]
AND [库存表].[年度]=@Year AND [库存表].[月份]=@Month AND R.[年度]=@Year AND R.[月份]=@Month
AND R.[日期]=DATEDIFF(DAY,CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month)+'-1',CONVERT(varchar,@Year)+'-'+CONVERT(varchar,@Month+1)+'-1')
----------------------------------------------------------------- --(3)生成该年该月的下一个月的[库存表].[期初库存数量]
DELETE FROM [库存表] WHERE [年度]=CASE @Month WHEN 12 THEN @Year+1 ELSE @Year END AND [月份]=CASE @Month WHEN 12 THEN 1 ELSE @Month+1 END INSERT INTO [库存表] ([仓库代码], [商品代码], [年度], [月份], [期初库存数量], [期末库存数量])
SELECT [仓库代码],[商品代码],CASE @Month WHEN 12 THEN @Year+1 ELSE @Year END AS [年度],
CASE @Month WHEN 12 THEN 1 ELSE @Month+1 END AS [月份], [期末库存数量] AS [期初库存数量],0 AS [期末库存数量]
FROM [库存表] WHERE [年度]=@Year AND [月份]=@Month --------------------------------------------------------------------------------
ENDGO
/*
用户自定义函数
名称:商品进销转存日报表_月
输入参数:
@CKDM 仓库代码
@Year 年度
@Month 月份
输出参数:返回表
调用范例:IF (SELECT TOP 1 [D1] FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)) IS NULL
BEGIN --如果报表记录不存在,则先用存储过程生成
EXEC SP_Get_SPJXZCRBB C01,2005,11
SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)
END
ELSE --如果报表记录存在,则直接提取
SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)注意:[D29]~[D31]列的动态绑定
作者:[email protected]
日期:2005年12月5日 04:14
*/
CREATE FUNCTION FN_Get_SPJXZCRBB_Month (@CKDM varchar(3),@Year int,@Month int)
RETURNS @Report TABLE (
[SPDM] varchar(3), [序号] int, [项目] varchar(20),
[D1] int, [D2] int, [D3] int, [D4] int,
[D5] int, [D6] int, [D7] int, [D8] int,
[D9] int, [D10] int, [D11] int, [D12] int,
[D13] int, [D14] int, [D15] int, [D16] int,
[D17] int, [D18] int, [D19] int, [D20] int,
[D21] int, [D22] int, [D23] int, [D24] int,
[D25] int, [D26] int, [D27] int, [D28] int,
[D29] int, [D30] int, [D31] int)
AS
BEGIN --逐商品统计_开始
DECLARE cur_SPDM CURSOR FOR SELECT [商品代码] FROM [商品表]
OPEN cur_SPDM
DECLARE @SPDM varchar(3),@I int
FETCH NEXT FROM cur_SPDM INTO @SPDM
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Report
SELECT @SPDM,1,'期初库存',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL
UNION SELECT @SPDM,2,'进',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL
UNION SELECT @SPDM,3,'销',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL
UNION SELECT @SPDM,4,'转(+入-出)',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL
UNION SELECT @SPDM,5,'期末库存',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL SET @I=1
WHILE @I<=5
BEGIN
UPDATE @Report SET [D1]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=1) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D2]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=2) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D3]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=3) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D4]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=4) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D5]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=5) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D6]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=6) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D7]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=7) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D8]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=8) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D9]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=9) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D10]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=10) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D11]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=11) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D12]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=12) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D13]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=13) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D14]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=14) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D15]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=15) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D16]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=16) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D17]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=17) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D18]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=18) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D19]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=19) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D20]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=20) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D21]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=21) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D22]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=22) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D23]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=23) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D24]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=24) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D25]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=25) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D26]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=26) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D27]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=27) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D28]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=28) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D29]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=29) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D30]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=30) WHERE [SPDM]=@SPDM AND [序号]=@I
UPDATE @Report SET [D31]=(SELECT CASE @I WHEN 1 THEN [期初余额] WHEN 2 THEN [进货] WHEN 3 THEN [销售] WHEN 4 THEN [转入]-[转出] WHEN 5 THEN [期末余额] END AS [数量] FROM [商品进销转存日报表] WHERE [仓库代码]=@CKDM AND [商品代码]=@SPDM AND [年度]=@Year AND [月份]=@Month AND [日期]=31) WHERE [SPDM]=@SPDM AND [序号]=@I SET @I=@I+1
END -------------------------------------- FETCH NEXT FROM cur_SPDM INTO @SPDM
END
CLOSE cur_SPDM
DEALLOCATE cur_SPDM
--逐商品统计_结束 RETURN
END SET @I=@I+1
END -------------------------------------- FETCH NEXT FROM cur_SPDM INTO @SPDM
END
CLOSE cur_SPDM
DEALLOCATE cur_SPDM
--逐商品统计_结束 RETURN
END
------------------看函数的调用范例即可------------------
(示例下载:http://www.anyitimes.com.cn/data/JXC_Sample.rar)
END -------------------------------------- FETCH NEXT FROM cur_SPDM INTO @SPDM
END
CLOSE cur_SPDM
DEALLOCATE cur_SPDM
--逐商品统计_结束 RETURN
END-----------------------------------------
上面这一段粘重复了,自己删掉;
你的转仓表设计有问题;
提什么钱啊,你TMD真俗。
-----------------------------------------
create table #dm_wz(dm int identity(1,1),wz_name varchar(10))
insert into #dm_wz(wz_name)
select 'abs' union all
select 'bbs' union all
select 'tvb' create table #dm_ck(dm int identity(1,1),ck_name varchar(10))
insert into #dm_ck(ck_name)
select 'wl' union all
select 'ql' union all
select 'fly' create table #insl(dm int identity(1,1),in_sl int,
in_date datetime,ckdm int)
insert into #insl(in_sl,in_date,ckdm)
select 20,'2005-08-02 00:08:00',2 union all
select 56,'2005-08-05 06:28:00',3 union all
select 87,'2005-08-07 12:38:40',1create table #out(dm int identity(1,1),o_sl int,
o_date datetime,ckdm int)
insert into #out(o_sl,o_date,ckdm)
select 25,'2005-08-06 00:08:00',2 union all
select 33,'2005-08-02 06:28:00',3 union all
select 57,'2005-08-04 12:38:40',1create table #zc(dm int identity(1,1),zc_sl int,
zc_date datetime,ckdm int)
insert into #zc(zc_sl,zc_date,ckdm)
select 22,'2005-08-03 00:08:00',2 union all
select 4,'2005-08-08 06:28:00',3 union all
select 52,'2005-08-02 12:38:40',1create table #cun(dm int identity(1,1),c_sl int,
ckdm int)
insert into #cun(c_sl,ckdm)
select 89,3 union all
select 34,2 union all
select 190,1
-----------------------------------
select d.dm as 商品代码 ,'进' as 类型,
SUM(Case day(in_date) When 1 Then in_sl Else 0 End) As N'1号',
SUM(Case day(in_date) When 2 Then in_sl Else 0 End) As N'2号',
SUM(Case day(in_date) When 3 Then in_sl Else 0 End) As N'3号',
SUM(Case day(in_date) When 4 Then in_sl Else 0 End) As N'4号',
SUM(Case day(in_date) When 5 Then in_sl Else 0 End) As N'5号',
SUM(Case day(in_date) When 6 Then in_sl Else 0 End) As N'6号',
SUM(Case day(in_date) When 7 Then in_sl Else 0 End) As N'7号',
SUM(Case day(in_date) When 8 Then in_sl Else 0 End) As N'8号'
from #dm_wz d
Inner Join #insl i
On d.dm=i.dm
Group By d.dmunion
select d.dm ,'销',
SUM(Case day(o_date) When 1 Then o_sl Else 0 End) As N'1号',
SUM(Case day(o_date) When 2 Then o_sl Else 0 End) As N'2号',
SUM(Case day(o_date) When 3 Then o_sl Else 0 End) As N'3号',
SUM(Case day(o_date) When 4 Then o_sl Else 0 End) As N'4号',
SUM(Case day(o_date) When 5 Then o_sl Else 0 End) As N'5号',
SUM(Case day(o_date) When 6 Then o_sl Else 0 End) As N'6号',
SUM(Case day(o_date) When 7 Then o_sl Else 0 End) As N'7号',
SUM(Case day(o_date) When 8 Then o_sl Else 0 End) As N'8号'
from #dm_wz d
Inner Join #out o
On d.dm=o.dm
Group By d.dm
union
select d.dm ,'转',
SUM(Case day(zc_date) When 1 Then zc_sl Else 0 End) As N'1号',
SUM(Case day(zc_date) When 2 Then zc_sl Else 0 End) As N'2号',
SUM(Case day(zc_date) When 3 Then zc_sl Else 0 End) As N'3号',
SUM(Case day(zc_date) When 4 Then zc_sl Else 0 End) As N'4号',
SUM(Case day(zc_date) When 5 Then zc_sl Else 0 End) As N'5号',
SUM(Case day(zc_date) When 6 Then zc_sl Else 0 End) As N'6号',
SUM(Case day(zc_date) When 7 Then zc_sl Else 0 End) As N'7号',
SUM(Case day(zc_date) When 8 Then zc_sl Else 0 End) As N'8号'
from #dm_wz d
Inner Join #zc z
On d.dm=z.dm
Group By d.dmdrop table #dm_wz,#dm_ck,#insl,#out,#zc,#cun
/*
-------------------------------------------------------商品代码 类型 1号 2号 3号 4号 5号 6号 7号 8号
1 存 0 -5 0 0 0 0 0 0
1 进 0 20 0 0 0 0 0 0
1 销 0 0 0 0 0 25 0 0
1 转 0 0 22 0 0 0 0 0
2 存 0 0 0 0 23 0 0 0
2 进 0 0 0 0 56 0 0 0
2 销 0 33 0 0 0 0 0 0
2 转 0 0 0 0 0 0 0 4
3 存 0 0 0 0 0 0 30 0
3 进 0 0 0 0 0 0 87 0
3 销 0 0 0 57 0 0 0 0
3 转 0 52 0 0 0 0 0 0
.............................*/
你TMD的白痴,跟狗一样,给我滚!
如果可以,我会汇款表示心意.帐号我会在E-MAIL你问你.我这个项目还是有点利润.参者有份.
msn:[email protected]
SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,11)--2#仓库,2005年11月的报表
SELECT * FROM FN_Get_SPJXZCRBB_Month('C02',2005,11)--1#仓库,2005年12月的报表
SELECT * FROM FN_Get_SPJXZCRBB_Month('C01',2005,12)--2#仓库,2005年12月的报表
SELECT * FROM FN_Get_SPJXZCRBB_Month('C02',2005,12)--如果跨月份,你就UNION后SUM--GROUP BY 即可。
使用了存储过程,自定义函数,游标,变量,种种情况都考虑了,我还没有看懂全部.不可否认,他是一种好方法,但是如果仅要体现进销存的内容,他的方法复杂了点,但他的方法体现了上存和结存,这是只可以查某一天的存储过程:是否可以改进一下为1-31的,
CREATE PROCEDURE Total_IOS(@StartDate datetime,@EndDate datetime, @Depot char(20), @Goods char(30)
) AS--参数:时间段,仓库,仓库类别,商品,商品类别declare @StartDate1 datetime,@EndDate1 datetime, @StartDate2 datetime,@EndDate2 datetime
declare @SQL varchar(6000),@Where1 varchar(300),@Where2 varchar(300)select @StartDate1=@StartDate,@EndDate1=@EndDate,@StartDate2=DateAdd(day,1,@EndDate),@EndDate2=cast(GetDate() as char)select @Where1='',@Where2='1=1'if @Depot<>''
begin
select @Where1='and (dm_ck.dm like ' + char(39) + rtrim(@Depot) + '%' + char(39) + ' or dm_ck.mc like ' + char(39) + '%' + rtrim(@Depot) + '%' + char(39) + ' or dm_ck.lbcode like ' + char(39) + '%' + rtrim(@Depot) + '%' + char(39) + ')'
endif @Goods<>''
begin
select @Where2=@Where2 + ' and (a.dm like ' + char(39) + rtrim(@Goods) + '%' + char(39) + ' or a.mc like ' + char(39) + '%' + rtrim(@Goods) + '%' + char(39) + ' or a.lbcode like ' + char(39) + '%' + rtrim(@Goods) + '%' + char(39) + ')'
endselect @SQL='
select wzdm,
wzmc,
dj,
(cun_sl + o_sl1 + zc_o_sl1 + o_sl2 + zc_o_sl2 - in_sl1 - zc_in_sl1 - in_sl2 - zc_in_sl2) as l_sl,
in_sl1 as in_sl,
o_sl1 as o_sl,
zc_in_sl1 as zc_in_sl,
zc_o_sl1 as zc_o_sl,
(cun_sl + o_sl2 + zc_o_sl2 - in_sl2 - zc_in_sl2) as c_sl
from
(select a.dm as wzdm,
a.mc as wzmc,
a.dj as dj,
isnull(b.in_sl,0) as in_sl1 ,
isnull(c.o_sl,0) as o_sl1,
isnull(d.sl1,0) as zc_o_sl1,
isnull(e.sl2,0) as zc_in_sl1,
isnull(f.in_sl,0) as in_sl2 ,
isnull(g.o_sl,0) as o_sl2,
isnull(h.sl1,0) as zc_o_sl2,
isnull(i.sl2,0) as zc_in_sl2,
isnull(j.cun_sl,0) as cun_sl
from dm_wz a left join (select wzdm, sum(in_sl)as in_sl, sum(in_jine) as in_jine
from ins1 inner join dm_ck on ins1.ckdm=dm_ck.dm
where DateDiff(day,in_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,in_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + '
group by wzdm) as b on a.dm=b.wzdm left join (select wzdm, sum(o_sl) as o_sl, sum(o_jine) as o_jine
from out inner join dm_ck on out.ckdm=dm_ck.dm
where DateDiff(day,o_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + '
group by wzdm) as c on a.dm=c.wzdm left join (select wzdm1, sum(sl1) as sl1, sum(jine1) as jine1
from zc inner join dm_ck on zc.ckdm1=dm_ck.dm
where DateDiff(day,o_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + '
group by wzdm1) as d on a.dm=d.wzdm1 left join (select wzdm2, sum(sl2) as sl2, sum(jine2) as jine2
from zc inner join dm_ck on zc.ckdm2=dm_ck.dm
where DateDiff(day,o_date,' + char(39) + convert(char,@StartDate1,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate1,20) + char(39) + ')>=0' + @where1 + '
group by wzdm2) as e on a.dm=e.wzdm2
left join (select wzdm, sum(in_sl)as in_sl, sum(in_jine) as in_jine
from ins1 inner join dm_ck on ins1.ckdm=dm_ck.dm
where DateDiff(day,in_date,' + char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,in_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + '
group by wzdm) as f on a.dm=f.wzdm left join (select wzdm, sum(o_sl) as o_sl, sum(o_jine) as o_jine
from out inner join dm_ck on out.ckdm=dm_ck.dm
where DateDiff(day,o_date,'+ char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + '
group by wzdm) as g on a.dm=g.wzdm left join (select wzdm1, sum(sl1) as sl1, sum(jine1) as jine1
from zc inner join dm_ck on zc.ckdm1=dm_ck.dm
where DateDiff(day,o_date,'+ char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + '
group by wzdm1) as h on a.dm=h.wzdm1 left join (select wzdm2, sum(sl2) as sl2, sum(jine2) as jine2
from zc inner join dm_ck on zc.ckdm2=dm_ck.dm
where DateDiff(day,o_date,'+ char(39) + convert(char,@StartDate2,20) + char(39) + ')<=0 and DateDiff(day,o_date,' + char(39) + convert(char,@EndDate2,20) + char(39) + ')>=0' + @where1 + '
group by wzdm2) as i on a.dm=i.wzdm2 left join (select wzdm, sum(sl) as cun_sl
from cun inner join dm_ck on cun.ckdm=dm_ck.dm
where 1=1' + @where1 + '
group by wzdm) as j on a.dm=j.wzdm where ' + @Where2 + ') as tmp'--print @SQLexec(@SQL)
--insert into 仓库表 select dm,mc from dm_ck
--insert into 商品表 select dm,mc from dm_wz
//insert into 商品进销转存日报表 select dm,mc from dm_ck
//insert into 库存表 select ckdm,wzdm,'2005','11'dm,mc from dm_wz
//2个表无对应内容,比如[库存表]无
[年度] [int] NOT NULL ,
[月份] [int] NOT NULL ,
[期初库存数量] [int] NOT NULL ,
[期末库存数量] [int] NOT NULL --insert into 转仓表 select zc_no,wzdm1,sl1,o_date,ckdm1,ckdm2 from zc
--insert into 进货表 select in_no,wzdm,in_sl,in_date,ckdm from ins1
--insert into 销售表 select o_no,wzdm,o_sl,o_date,ckdm from out
其他表导入后,执行SELECT * FROM FN_Get_SPJXZCRBB_Month('0001',2005,11)
5分钟数据还没有出来,商品代码有12000条,其他演示数据都只有几十条,速度也是个问题