各位大侠:小弟现在在做一张物料收发明细报表时,遇到了如下难题,恳请赐教. 问题描述如下,
现有如下数据的表:
日期 单据类型 物料 仓库 期初数量 收入数量 发出数量 结存数量
2009-01-01 外购入库 A物料 A仓库 10 1 0 0
2009-02-01 其他入库 A物料 A仓库 0 3 0 0
2009-03-01 销售出库 A物料 A仓库 0 0 2 0
2009-01-02 外购入库 B物料 B仓库 20 4 0 0
2009-02-02 其他入库 B物料 B仓库 0 2 0 0
2009-03-02 销售出库 B物料 B仓库 0 0 4 0
...
如何写出求出每种物料每日的期初数量和结存数量的SQL? 能得到如下的结果?
日期 单据类型 物料 仓库 期初数量 收入数量 发出数量 结存数量
2009-01-01 外购入库 A物料 A仓库 10 1 0 11
2009-02-01 其他入库 A物料 A仓库 11 3 0 14
2009-03-01 销售出库 A物料 A仓库 14 0 2 12
2009-01-02 外购入库 B物料 B仓库 20 4 0 24
2009-02-02 其他入库 B物料 B仓库 24 2 0 26
2009-03-02 销售出库 B物料 B仓库 26 0 4 22
...
表结构:
create table tt
(
FDate datetime,
FBilltype varchar(50),
FItem varchar(50),
FStock varchar(50),
FBegQty Decimal(28,10),
FInQty Decimal(28,10),
FOutQty Decimal(28,10),
FEndQty Decimal(28,10)
)上次有CSDN的热心网友给我的按比较日期大小的方法:
如下:
if object_id('[TB]') is not null drop table [TB]
create table TB
(
FDate varchar(10),
FBilltype varchar(50),
FItem varchar(50),
FStock varchar(50),
FBegQty Decimal(28,10),
FInQty Decimal(28,10),
FOutQty Decimal(28,10),
FEndQty Decimal(28,10)
)
insert [TB]
select '2009-01-01','外购入库','A物料','A仓库',10,1,0,0 union all
select '2009-02-01','其他入库','A物料','A仓库',0,3,0,0 union all
select '2009-03-01','销售出库','A物料','A仓库',0,0,2,0 union all
select '2009-01-02','外购入库','B物料','B仓库',20,4,0,0 union all
select '2009-02-02','其他入库','B物料','B仓库',0,2,0,0 union all
select '2009-03-02','销售出库','B物料','B仓库',0,0,4,0select 日期=FDate,
单据类型=FBilltype,
物料=FItem,
仓库=FStock,
期初数量=isnull((select sum(FBegQty+FInQty-FOutQty) from TB where t.FItem=FItem and t.FDate>FDate),FBegQty),
收入数量=FInQty,
发出数量=FOutQty,
结存数量=isnull((select sum(FBegQty+FInQty-FOutQty) from TB where t.FItem=FItem and t.FDate>FDate),FBegQty)+FInQty-FOutQty
from TB t
----
但是有一个新问题:
如果同一物料在同一天做了两张或以下的单据,同时日期没有小时,分,秒,全部是形如:2009-08-01 00:00:00
则用 t.FDate > FDate ,同一天计算出来的期初数就不对了,请问各位大侠有何解决办法?
现有如下数据的表:
日期 单据类型 物料 仓库 期初数量 收入数量 发出数量 结存数量
2009-01-01 外购入库 A物料 A仓库 10 1 0 0
2009-02-01 其他入库 A物料 A仓库 0 3 0 0
2009-03-01 销售出库 A物料 A仓库 0 0 2 0
2009-01-02 外购入库 B物料 B仓库 20 4 0 0
2009-02-02 其他入库 B物料 B仓库 0 2 0 0
2009-03-02 销售出库 B物料 B仓库 0 0 4 0
...
如何写出求出每种物料每日的期初数量和结存数量的SQL? 能得到如下的结果?
日期 单据类型 物料 仓库 期初数量 收入数量 发出数量 结存数量
2009-01-01 外购入库 A物料 A仓库 10 1 0 11
2009-02-01 其他入库 A物料 A仓库 11 3 0 14
2009-03-01 销售出库 A物料 A仓库 14 0 2 12
2009-01-02 外购入库 B物料 B仓库 20 4 0 24
2009-02-02 其他入库 B物料 B仓库 24 2 0 26
2009-03-02 销售出库 B物料 B仓库 26 0 4 22
...
表结构:
create table tt
(
FDate datetime,
FBilltype varchar(50),
FItem varchar(50),
FStock varchar(50),
FBegQty Decimal(28,10),
FInQty Decimal(28,10),
FOutQty Decimal(28,10),
FEndQty Decimal(28,10)
)上次有CSDN的热心网友给我的按比较日期大小的方法:
如下:
if object_id('[TB]') is not null drop table [TB]
create table TB
(
FDate varchar(10),
FBilltype varchar(50),
FItem varchar(50),
FStock varchar(50),
FBegQty Decimal(28,10),
FInQty Decimal(28,10),
FOutQty Decimal(28,10),
FEndQty Decimal(28,10)
)
insert [TB]
select '2009-01-01','外购入库','A物料','A仓库',10,1,0,0 union all
select '2009-02-01','其他入库','A物料','A仓库',0,3,0,0 union all
select '2009-03-01','销售出库','A物料','A仓库',0,0,2,0 union all
select '2009-01-02','外购入库','B物料','B仓库',20,4,0,0 union all
select '2009-02-02','其他入库','B物料','B仓库',0,2,0,0 union all
select '2009-03-02','销售出库','B物料','B仓库',0,0,4,0select 日期=FDate,
单据类型=FBilltype,
物料=FItem,
仓库=FStock,
期初数量=isnull((select sum(FBegQty+FInQty-FOutQty) from TB where t.FItem=FItem and t.FDate>FDate),FBegQty),
收入数量=FInQty,
发出数量=FOutQty,
结存数量=isnull((select sum(FBegQty+FInQty-FOutQty) from TB where t.FItem=FItem and t.FDate>FDate),FBegQty)+FInQty-FOutQty
from TB t
----
但是有一个新问题:
如果同一物料在同一天做了两张或以下的单据,同时日期没有小时,分,秒,全部是形如:2009-08-01 00:00:00
则用 t.FDate > FDate ,同一天计算出来的期初数就不对了,请问各位大侠有何解决办法?
INSERT @TB
SELECT '2009-01-01', N'外购入库', N'A物料', N'A仓库', 10, 1, 0, 0 UNION ALL
SELECT '2009-02-01', N'其他入库', N'A物料', N'A仓库', 0, 3, 0, 0 UNION ALL
SELECT '2009-03-01', N'销售出库', N'A物料', N'A仓库', 0, 0, 2, 0 UNION ALL
SELECT '2009-01-02', N'外购入库', N'B物料', N'B仓库', 20, 4, 0, 0 UNION ALL
SELECT '2009-02-02', N'其他入库', N'B物料', N'B仓库', 0, 2, 0, 0 UNION ALL
SELECT '2009-03-02', N'销售出库', N'B物料', N'B仓库', 0, 0, 4, 0DECLARE @STK INT, @BAL INT
DECLARE @LOC NVARCHAR(3), @ITEM NVARCHAR(3)UPDATE @TB
SET @STK=CASE WHEN @LOC=仓库 AND @ITEM=物料 THEN @BAL ELSE 期初数量 END,
@BAL=@STK+收入数量-发出数量,
@LOC=仓库,
@ITEM=物料,
期初数量=@STK,
结存数量=@BALSELECT *
FROM @TB
/*
日期 单据类型 物料 仓库 期初数量 收入数量 发出数量 结存数量
------------------------------------------------------ ---- ---- ---- ----------- ----------- ----------- -----------
2009-01-01 00:00:00.000 外购入库 A物料 A仓库 10 1 0 11
2009-02-01 00:00:00.000 其他入库 A物料 A仓库 11 3 0 14
2009-03-01 00:00:00.000 销售出库 A物料 A仓库 14 0 2 12
2009-01-02 00:00:00.000 外购入库 B物料 B仓库 20 4 0 24
2009-02-02 00:00:00.000 其他入库 B物料 B仓库 24 2 0 26
2009-03-02 00:00:00.000 销售出库 B物料 B仓库 26 0 4 22(6 row(s) affected)
*/
下面是算法:关键在这里:
where t.FItemID=FItemID and (t.FDate>FDate or (t.FDate = FDate and t.FRowID>FRowID))) 其中FRowID,是自增列,如果日期相同,再去比较自增列的大小。
十分感谢SQL77提供的思路。存储过程中的算法:计算日初数量与日结存数量。
Insert into #CclT200(FDate,FBillNo,FTranType,FNote,FStockName,FStockPlaceName,FItemID,FBatchNo,FBegQty,
FBegBal,FInQty,FOutQty,FInAmount,FOutAmount,FEndQty,FEndBal)
Select t.FDate,t.FBillNo,t.FTranType,t.FNote,t.FStockName,t.FStockPlaceName,t.FItemID,t.FBatchNo,
FBegQty = isnull((select sum(FBegQty+FInQty-FOutQty) from #CclT100
where t.FItemID=FItemID and (t.FDate>FDate or (t.FDate = FDate and t.FRowID>FRowID))) ,FBegQty),
FBegBal = isnull((select sum(FBegBal+FInAmount-FOutAmount) from #CclT100
where t.FItemID=FItemID and (t.FDate>FDate or (t.FDate = FDate and t.FRowID>FRowID))),FBegBal),
FInQty,FOutQty,FInAmount,FOutAmount,
FEndQty = isnull((select sum(FBegQty+FInQty-FOutQty) from #CclT100
where t.FItemID=FItemID and (t.FDate>FDate or (t.FDate = FDate and t.FRowID>FRowID))),FBegQty)+FInQty-FOutQty,
FEndBal = isnull((select sum(FBegBal+FInAmount-FOutAmount) from #CclT100
where t.FItemID=FItemID and (t.FDate>FDate or (t.FDate = FDate and t.FRowID>FRowID))),FBegBal)+FInAmount-FOutAmount
From #CclT100 t