动态时间段收发存报表
来源：互联网 发布：手绘板mac驱动 编辑：程序博客网 时间：2024/05/02 01:14
http://topic.csdn.net/u/20090831/10/a850c8a4-c733-459f-8d33-a9523e73ad52.html
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:IO_STORE
If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]
-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop FUNCTION [R_I_INOUTSTOR]
Go
CREATE FUNCTION R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
AS
BEGIN
INSERT @TempTable
SELECT STORE,@FROMDATE,'期初',GOODS,
sum(case ISIND when 1 then QT else -QT end),0,0,
sum(case ISIND when 1 then QT else -QT end)
FROM [IO_STORE]
WHERE [BD]<@FROMDATE
GROUP BY STORE,GOODS
INSERT @TempTable
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,BD,BC,GOODS
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT
FROM @TempTable t
UPDATE @TempTable SET
CQT=SQT+IQT-OQT
WHERE CQT=0
INSERT @TempTable
SELECT STORE,
(select top 1 BD from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC),
'结存',GOODSID,
(select SQT from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID and BC='期初'),
sum(IQT),sum(OQT),
(select top 1 CQT from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC)
FROM @TempTable t
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,GOODSID
RETURN
END
GO
--调用查询
SELECT STORE 仓库,CONVERT(VARCHAR(10),BD,23) 日期,BC 单号, GOODSID 货品,SQT 期初数,IQT 收入数,OQT 发出数,CQT 结存数 FROM R_I_INOUTSTOR('2009-07-03','2009-07-28') ORDER BY STORE,GOODSID,ID /* 仓库 日期 单号 货品 期初数 收入数 发出数 结存数 ---------- ---------- ---------- ---------- ----- ----- ----- ----- 半成品仓 2009-07-03 期初 16081D 350 0 0 350 半成品仓 2009-07-05 090705001 16081D 350 0 30 320 半成品仓 2009-07-18 090718003 16081D 320 90 0 410 半成品仓 2009-07-18 090718004 16081D 410 200 0 610 半成品仓 2009-07-18 结存 16081D 350 290 30 610 半成品仓 2009-07-03 期初 16082A 50 0 0 50 半成品仓 2009-07-06 090706002 16082A 50 0 20 30 半成品仓 2009-07-18 090718006 16082A 30 0 20 10 半成品仓 2009-07-18 结存 16082A 50 0 40 10 半成品仓 2009-07-15 090715007 16083K 0 80 0 80 半成品仓 2009-07-23 090723007 16083K 80 0 35 45 半成品仓 2009-07-23 结存 16083K NULL 80 35 45 成品仓 2009-07-03 期初 16081D 50 0 0 50 成品仓 2009-07-03 090703008 16081D 50 77 0 127 成品仓 2009-07-28 090728026 16081D 127 0 20 107 成品仓 2009-07-28 结存 16081D 50 77 20 107 (16 行受影响) */
--增强版：http://topic.csdn.net/u/20100520/20/a3055dfd-838b-44d5-a37c-f2d26e1b226f.html?14076
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-20 23:11:48
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [VI_INSTORE]
IF OBJECT_ID('[VI_INSTORE]') IS NOT NULL
DROP TABLE [VI_INSTORE]
GO
CREATE TABLE [VI_INSTORE] ([BILLID] [int],[STOREID] [nvarchar](10),[GOODSID] [nvarchar](10),[BILLDATE] [datetime],[BILLCODE] [nvarchar](10),[QTY] [int],[REMARK1] [nvarchar](10))
INSERT INTO [VI_INSTORE]
SELECT '1001','原材料仓','G001','2010-05-01','IN001','100','AB' UNION ALL
SELECT '1002','原材料仓','G002','2010-05-03','IN002','300','C' UNION ALL
SELECT '1003','原材料仓','G003','2010-05-03','IN003','200','D' UNION ALL
SELECT '1003','原材料仓','G003','2010-05-03','IN003','70','E' UNION ALL
SELECT '1004','原材料仓','G003','2010-05-30','IN009','40','F' UNION ALL
SELECT '1005','半成品仓','G004','2010-05-30','IN008','33','K'
--> 生成测试数据表: [VI_OUTSTORE]
IF OBJECT_ID('[VI_OUTSTORE]') IS NOT NULL
DROP TABLE [VI_OUTSTORE]
GO
CREATE TABLE [VI_OUTSTORE] ([BILLID] [int],[STOREID] [nvarchar](10),[GOODSID] [nvarchar](10),[BILLDATE] [datetime],[BILLCODE] [nvarchar](10),[QTY] [int],[REMARK1] [nvarchar](10))
INSERT INTO [VI_OUTSTORE]
SELECT '3001','原材料仓','G001','2010-05-02','OU001','30',null UNION ALL
SELECT '3002','原材料仓','G003','2010-05-05','OU002','100',null UNION ALL
SELECT '3003','半成品仓','G004','2010-05-30','OU003','30',null
--SELECT * FROM [VI_INSTORE]
--SELECT * FROM [VI_OUTSTORE]
-->SQL查询如下:
If not object_id('[VINSTORE]') is null
Drop FUNCTION VINSTORE
Go
CREATE FUNCTION VINSTORE(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE
(
ID int identity,
STOREID VARCHAR(80), --仓库
[BILLDATE] VARCHAR(10), --日期
[BILLCODE] VARCHAR(80),--单号
REMARK1 VARCHAR(100),--备注
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
AS
BEGIN
INSERT @TempTable
SELECT [STOREID],CONVERT(CHAR,@FROMDATE,23),'期初',MAX([REMARK1]),[GOODSID],
sum(case ISIND when 1 then [QTY] else -[QTY] end),0,0,
sum(case ISIND when 1 then [QTY] else -[QTY] end)
FROM (
SELECT ISIND=1,* FROM VI_INSTORE
UNION ALL
SELECT IDIND=0,* FROM [VI_OUTSTORE]
) [IO_STORE]
WHERE [BILLDATE]<@FROMDATE
GROUP BY [STOREID],[GOODSID]
INSERT @TempTable
SELECT [STOREID],CONVERT(CHAR,@FROMDATE,23),'期初','',[GOODSID],0,0,0,0
FROM (
SELECT ISIND=1,* FROM VI_INSTORE
UNION ALL
SELECT IDIND=0,* FROM [VI_OUTSTORE]
) [IO_STORE]
WHERE NOT EXISTS(SELECT 1 FROM @TempTable WHERE STOREID=[IO_STORE].STOREID AND GOODSID=[IO_STORE].GOODSID)
GROUP BY [STOREID],[GOODSID]
INSERT @TempTable
SELECT [STOREID],CONVERT(CHAR,[BILLDATE],23),[BILLCODE],MAX([REMARK1]),[GOODSID],0,
SUM(case ISIND WHEN 1 then [QTY] else 0 end) IQT,
SUM(case ISIND WHEN 0 then [QTY] else 0 end) OQT,
0
FROM (
SELECT ISIND=1,* FROM VI_INSTORE
UNION ALL
SELECT IDIND=0,* FROM [VI_OUTSTORE]
) [IO_STORE]
WHERE [BILLDATE] BETWEEN @FROMDATE AND @TODATE
GROUP BY [STOREID],[BILLDATE],[BILLCODE],[GOODSID]
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where [STOREID]=t.[STOREID] AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT
FROM @TempTable t
UPDATE @TempTable SET
CQT=SQT+IQT-OQT
WHERE CQT=0
INSERT @TempTable
SELECT [STOREID],
(SELECT TOP 1 CONVERT(CHAR,[BILLDATE],23) FROM @TEMPTABLE WHERE [STOREID]=T.[STOREID] AND GOODSID=T.GOODSID ORDER BY ID DESC),
'结存','',GOODSID,(SELECT SQT FROM @TEMPTABLE WHERE [STOREID]=T.[STOREID] AND GOODSID=T.GOODSID AND [BILLCODE]='期初'),
SUM(IQT),SUM(OQT),(SELECT TOP 1 CQT FROM @TEMPTABLE WHERE [STOREID]=T.[STOREID] AND GOODSID=T.GOODSID ORDER BY ID DESC)
FROM @TempTable t
WHERE [BILLDATE] BETWEEN @FROMDATE AND @TODATE
GROUP BY [STOREID],GOODSID
RETURN
END
GO
--调用查询
SELECT STOREID 仓库,BILLDATE 日期,BILLCODE 单号,REMARK1 备注,
GOODSID 货品,SQT 期初数,IQT 收入数,OQT 发出数,CQT 结存数
FROM VINSTORE('2010-05-01','2010-05-31')
ORDER BY GOODSID,STOREID,ID
/*
仓库 日期 单号 备注 货品 期初数 收入数 发出数 结存数
原材料仓 2010-05-01 期初 G001 0 0 0 0
原材料仓 2010-05-01 IN001 AB G001 0 100 0 100
原材料仓 2010-05-02 OU001 NULL G001 100 0 30 70
原材料仓 2010-05-02 结存 G001 0 100 30 70
原材料仓 2010-05-01 期初 G002 0 0 0 0
原材料仓 2010-05-03 IN002 C G002 0 300 0 300
原材料仓 2010-05-03 结存 G002 0 300 0 300
原材料仓 2010-05-01 期初 G003 0 0 0 0
原材料仓 2010-05-03 IN003 E G003 0 270 0 270
原材料仓 2010-05-05 OU002 NULL G003 270 0 100 170
原材料仓 2010-05-30 IN009 F G003 170 40 0 210
原材料仓 2010-05-30 结存 G003 0 310 100 210
半成品仓 2010-05-01 期初 G004 0 0 0 0
半成品仓 2010-05-30 IN008 K G004 0 33 0 33
半成品仓 2010-05-30 OU003 NULL G004 33 0 30 3
半成品仓 2010-05-30 结存 G004 0 33 30 3
*/
SELECT STOREID 仓库,BILLDATE 日期,BILLCODE 单号,REMARK1 备注,
GOODSID 货品,SQT 期初数,IQT 收入数,OQT 发出数,CQT 结存数
FROM VINSTORE('2010-05-03','2010-05-31')
ORDER BY GOODSID,STOREID,ID
/*
仓库 日期 单号 备注 货品 期初数 收入数 发出数 结存数
原材料仓 2010-05-03 期初 AB G001 70 0 0 70
原材料仓 2010-05-03 结存 G001 70 0 0 70
原材料仓 2010-05-03 期初 G002 0 0 0 0
原材料仓 2010-05-03 IN002 C G002 0 300 0 300
原材料仓 2010-05-03 结存 G002 0 300 0 300
原材料仓 2010-05-03 期初 G003 0 0 0 0
原材料仓 2010-05-03 IN003 E G003 0 270 0 270
原材料仓 2010-05-05 OU002 NULL G003 270 0 100 170
原材料仓 2010-05-30 IN009 F G003 170 40 0 210
原材料仓 2010-05-30 结存 G003 0 310 100 210
半成品仓 2010-05-03 期初 G004 0 0 0 0
半成品仓 2010-05-30 IN008 K G004 0 33 0 33
半成品仓 2010-05-30 OU003 NULL G004 33 0 30 3
半成品仓 2010-05-30 结存 G004 0 33 30 3
*/
SELECT STOREID 仓库,BILLDATE 日期,BILLCODE 单号,REMARK1 备注,
GOODSID 货品,SQT 期初数,IQT 收入数,OQT 发出数,CQT 结存数
FROM VINSTORE('2010-06-01','2010-06-30')
ORDER BY GOODSID,STOREID,ID
/*
仓库 日期 单号 备注 货品 期初数 收入数 发出数 结存数
原材料仓 2010-06-01 期初 AB G001 70 0 0 70
原材料仓 2010-06-01 结存 G001 70 0 0 70
原材料仓 2010-06-01 期初 C G002 300 0 0 300
原材料仓 2010-06-01 结存 G002 300 0 0 300
原材料仓 2010-06-01 期初 F G003 210 0 0 210
原材料仓 2010-06-01 结存 G003 210 0 0 210
半成品仓 2010-06-01 期初 K G004 3 0 0 3
半成品仓 2010-06-01 结存 G004 3 0 0 3
*/
- 动态时间段收发存报表
- SAP收发存报表程序
- SAP收发存报表程序
- 时间段报表
- SAP 库存物资收发存报表主要思路
- 库存管理-历史库存和收发存系列-俄罗斯库存报表J3RFLVMOBVED1
- 使用事务代码MB51+Excel中的数据透视表实现 收发存报表
- 存货收发存应用实例
- mybatis的时间段动态查询
- 简单的动态广播收发
- 时间段
- 动态取当天时间段内的条件
- ERP 物流_账表_仓库收发存_月报
- 库存管理-历史库存和收发存系列-MB5B
- 产销存报表直接在MB5B上取数
- 在数据库中保存FastReport .Net报表
- 动态水晶报表
- 动态水晶报表
- SQL CE 安装
- 翻译2440 CLOCK & POWER MANAGEMENT
- WinRAR密码破解
- 从数据库中读出国家和省份，级联
- __FILE__ __LINE__
- 动态时间段收发存报表
- iframe链接到父窗口
- 赚点论坛分
- FireBird批量数据导入
- Linux下网速慢的问题
- Oracel 10g 的数据库闪回测试
- 如何使用LdapConnection 类 （1）链接 Ldap服务器
- Yiwu,golden card for the China-made
- Linux下的Oracle 10g安装及配置
