现在有表
tStorage(期初数据表)ID Item Qty
1 01-001-001-01 100
2 01-001-002-01 200
......
--------------------------------------------------------
tInStorage(入库主表)
AutoAccout Invoice InDate
20050820001 369258 2005-8-20
20050821001 369260 2005-8-21
20050822001 369261 2005-8-22
......
-------------------------------------------------------
tInStorageSub(入库从表)
AutoAccout Item Qty
20050820001 01-001-001-01 250
20050820001 01-001-002-01 100
20050821001 01-001-001-01 300
20050822001 01-001-002-01 300
......
------------------------------------------------------
tOutStorage(出库主表)
AutoAccout Invoice OutDate
20050820001 249258 2005-8-20
20050820002 249259 2005-8-20
20050821001 249260 2005-8-21
20050823001 249261 2005-8-23
......
-----------------------------------------------------
tOutStorageSub(出库从表)
AutoAccout Item Qty
20050820001 01-001-001-01 100
20050820002 01-001-002-01 100
20050821001 01-001-001-01 50
20050823001 01-001-001-01 300
......
----------------------------------------------------
求下面的的结果:录入Item:01-001-001-01日期 期初 入库 出库 结存
100 0 0 100
2005-8-20 0 250 100 250
2005-8-21 0 300 50 500
2005-8-23 0 0 300 200
......录入Item:01-001-001-01
与上面相同
还有下面的结果:
录入某个日期之间(2005-8-1到2005-8-31)Item 期初 入库 出库 结存
01-001-001-01 100 550 450 200
01-001-002-01 200 400 100 500
......
tStorage(期初数据表)ID Item Qty
1 01-001-001-01 100
2 01-001-002-01 200
......
--------------------------------------------------------
tInStorage(入库主表)
AutoAccout Invoice InDate
20050820001 369258 2005-8-20
20050821001 369260 2005-8-21
20050822001 369261 2005-8-22
......
-------------------------------------------------------
tInStorageSub(入库从表)
AutoAccout Item Qty
20050820001 01-001-001-01 250
20050820001 01-001-002-01 100
20050821001 01-001-001-01 300
20050822001 01-001-002-01 300
......
------------------------------------------------------
tOutStorage(出库主表)
AutoAccout Invoice OutDate
20050820001 249258 2005-8-20
20050820002 249259 2005-8-20
20050821001 249260 2005-8-21
20050823001 249261 2005-8-23
......
-----------------------------------------------------
tOutStorageSub(出库从表)
AutoAccout Item Qty
20050820001 01-001-001-01 100
20050820002 01-001-002-01 100
20050821001 01-001-001-01 50
20050823001 01-001-001-01 300
......
----------------------------------------------------
求下面的的结果:录入Item:01-001-001-01日期 期初 入库 出库 结存
100 0 0 100
2005-8-20 0 250 100 250
2005-8-21 0 300 50 500
2005-8-23 0 0 300 200
......录入Item:01-001-001-01
与上面相同
还有下面的结果:
录入某个日期之间(2005-8-1到2005-8-31)Item 期初 入库 出库 结存
01-001-001-01 100 550 450 200
01-001-002-01 200 400 100 500
......
set @Storage='01-001-001-01'
select '' as 日期, Qty, 0, 0, Qty from tStorage where Item=@Stroage
union
select InOutDate as 日期, 0 as 期初,
(select sum(Qty) from tInStorage a, tInStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate=x.InOutDate) as 入库,
(select sum(Qty) from tOutStorage a, tOutStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate=x.InOutDate) as 出库,,
(select Qty from tStorage where Item=@Stroage) +
(select sum(Qty) from tInStorage a, tInStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate<=x.InOutDate) +
(select sum(Qty) from tOutStorage a, tOutStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate<=x.InOutDate) as 结存
(select indate as InOutDate from tInStorage
union
Select OutDate as InOutDate from tOutStorage)x
1.
declare @Storage
set @Storage='01-001-001-01'
select '' as 日期, Qty, 0, 0, Qty from tStorage where Item=@Stroage
union
select InOutDate as 日期, 0 as 期初,
(select sum(Qty) from tInStorage a, tInStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate=x.InOutDate) as 入库,
(select sum(Qty) from tOutStorage a, tOutStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate=x.InOutDate) as 出库,,
(select Qty from tStorage where Item=@Stroage) +
(select sum(Qty) from tInStorage a, tInStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Indate<=x.InOutDate) +
(select sum(Qty) from tOutStorage a, tOutStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=@Storge and a.Outdate<=x.InOutDate) as 结存
(select indate as InOutDate from tInStorage
union
Select OutDate as InOutDate from tOutStorage)x2.
declare @vDate1 varchar(10)
declare @vDate2 varchar(10)set @vDate1='2005-08-01'
set @vDate2='2005-08-31'
select 期初, 入库, 出库, 期初+入库-出库 as 结存
from
(select Qty as 期初,
(select sum(Qty) from tInStorage a, tInStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=x.Item and a.Indate>=@vDate1 and a.Indate <=@vDate2) as 入库,
(select sum(Qty) from tOutStorage a, tOutStroageSub b where a.AutoAccout=b.AutoAccout and b.Item=x.Item and a.Outdate>=@vDate1 and a.Outdate <=@vDate2) as 出库,
from tStorage x) y
能不能改写一个呀
根据上面的方法,你可以分段处理了!
不过见意你多建一个进出明细表,统计时只要统计这一个表就好,也不必跑这儿找一点数据,又跑那儿找一点数据!查询起来也比较麻烦!
如果多建一个进出明细表,我的入库单、出库单又怎么控制呢?
是否入库出库的时候同时写进‘进出明细表’呢?
如果多建一个进出明细表,我的入库单、出库单又怎么控制呢?
是否入库出库的时候同时写进‘进出明细表’呢?
--------------------------------------------
在进出明细表中,必然要有个进出库的标志字段
我说下自己做过的简单模型:
我只有两个表:历史记录表和库存表,历史记录表大概就是枫叶说的进出明细表,作用是记录所有出入库记录动作。库存表就是记录库存,无论是出还是入都必然要修改这个表
楼主还是自己写循环吧!