create table paylog ( gold int not null, paydate datetime not null, des nvarchar(10) not null ) insert into paylog select '80','2010-09-10 13:18','付费' union all select '100','2010-09-11 14:18','赠送' union all select '30','2010-09-12 14:20','赠送' union all select '40','2010-09-13 14:20','付费' union all select '90','2010-09-14 14:20','付费' union all select '30','2010-09-15 14:20','赠送' gocreate table costlog ( gold int not null, costdate datetime not null ) insert into costlog select '50','2010-09-12 14:00' union all select '80','2010-09-13 14:18' union all select '10','2010-09-14 14:20' union all select '60','2010-09-15 14:20' union all select '50','2010-09-16 14:20' union all select '1000','2010-09-17 14:20' --结果 /* gold paydate des gold costdate details 80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50 80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30 100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50 100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10 100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40 30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20 30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10 40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40 90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90 30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30 */ --1:declare @i int set @i=1 while @i<=2000 begininsert into num select @iset @i=@i+1 end go;with pay_t as (select row_number() over (order by paydate) as indexno,a.* from paylog a,num b where a.gold>=b.indexno ),cost_t as ( select row_number() over (order by costdate) as indexno,a.* from costlog a,num b where a.gold>=b.indexno )select a.gold,a.paydate,a.des,b.gold,b.costdate,count(1) as details from pay_t a,cost_t b where a.indexno=b.indexno group by a.gold,a.paydate,a.des,b.gold,b.costdate order by b.costdate,a.paydate--2:with t1 as ( select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from paylog where paydate <= a.paydate) from paylog a ) ,t2 as ( select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from costlog where costdate <= a.costdate) from costlog a ) ,t3 as ( select a.*,bid1=b.id from t1 a cross apply (select top (1) * from t2 where sumgold >= a.sumgold order by sumgold) b ) ,t4 as ( select a.*,bid2=isnull(b.bid1,1) from t3 a left join t3 b on a.id = b.id + 1 ) ,t5 as ( select paygold=a.gold,a.paydate,a.des,costgold=b.gold,b.costdate,details= case when a.sumgold>=b.sumgold then case when a.gold-a.sumgold+b.sumgold >= b.gold then b.gold else a.gold-a.sumgold+b.sumgold end else case when b.gold-b.sumgold+a.sumgold >= a.gold then a.gold else b.gold-b.sumgold+a.sumgold end end from t4 a join t2 b on b.id between a.bid2 and a.bid1 ) select * from t5 where details > 0--3:create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int) godeclare my_cursor cursor scroll for select * from paylog open my_cursor declare @gold int declare @paydate datetime declare @des nvarchar(10) set @gold = 0 fetch next from my_cursor into @gold,@paydate,@des while (@@fetch_status = 0) begin declare next_cursor cursor scroll for select * from costlog open next_cursor declare @go int declare @costdate datetime declare @details int declare @gocun int declare @decun int set @decun = 0 set @go = 0 set @details = 0 set @gocun = @gold fetch next from next_cursor into @go,@costdate set @gocun = @gocun - @go while(@@fetch_status = 0) begin if(@gocun = 0) begin set @details = @gold insert into cun select @gold,@paydate,@des,@go,@costdate,@details fetch next from my_cursor into @gold,@paydate,@des fetch next from next_cursor into @go,@costdate set @gocun = @gold - @go set @decun = 0 end if(@gocun > 0) begin set @details = @go - @decun insert into cun select @gold,@paydate,@des,@go,@costdate,@details fetch next from next_cursor into @go,@costdate set @gocun = @gocun - @go set @decun = 0 end if(@gocun < 0) begin set @details = @go + @gocun - @decun insert into cun select @gold,@paydate,@des,@go,@costdate,@details fetch next from my_cursor into @gold,@paydate,@des set @gocun = @gold + @gocun set @decun = @details end end close next_cursor deallocate next_cursor end close my_cursor deallocate my_cursor select * from cun
先进先出的做法,无非是先入库的商品,然后先销售出来。 在软件实现,有两种做法: 第一种,用批次法,这种对开发方式比较简单,就是所有进项都有一个批号,在销售的时候按批号日期排序来选择。如进仓: 批号 日期 商品 数量 单价 201101 01-02 A 20 2 201101 01-02 B 30 3 201102 01-03 A 40 2.5然后在销售的时候从进仓里选择,按批号来选择,同一个商品A,201101批的进价是2,201102批的进价是2.5,销售的时候按先进先出的原则,先选择201101批,销售完后再选择201102批。第二种,用软件方法来计算,如进仓:日期 商品 数量 单价 已销售数量 销售单价 01-02 A 20 2 10 3.5 01-02 B 30 3 20 5 01-03 A 40 2.5已销售数量从销售单据里反填进入,在反填的时候,根据日期排序,一行一行进行反填,一定要注意数量,只要相等了,就下一行数据反填已销售数量和销售单价。
gold int not null,
paydate datetime not null,
des nvarchar(10) not null
)
insert into paylog
select '80','2010-09-10 13:18','付费'
union all
select '100','2010-09-11 14:18','赠送'
union all
select '30','2010-09-12 14:20','赠送'
union all
select '40','2010-09-13 14:20','付费'
union all
select '90','2010-09-14 14:20','付费'
union all
select '30','2010-09-15 14:20','赠送'
gocreate table costlog (
gold int not null,
costdate datetime not null
)
insert into costlog
select '50','2010-09-12 14:00'
union all
select '80','2010-09-13 14:18'
union all
select '10','2010-09-14 14:20'
union all
select '60','2010-09-15 14:20'
union all
select '50','2010-09-16 14:20'
union all
select '1000','2010-09-17 14:20'
--结果
/*
gold paydate des gold costdate details
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30
*/
--1:declare @i int
set @i=1
while @i<=2000
begininsert into num
select @iset @i=@i+1
end
go;with pay_t as (select row_number() over (order by paydate) as indexno,a.*
from paylog a,num b where a.gold>=b.indexno
),cost_t as (
select row_number() over (order by costdate) as indexno,a.*
from costlog a,num b where a.gold>=b.indexno
)select a.gold,a.paydate,a.des,b.gold,b.costdate,count(1) as details
from pay_t a,cost_t b where a.indexno=b.indexno group by a.gold,a.paydate,a.des,b.gold,b.costdate
order by b.costdate,a.paydate--2:with t1 as
(
select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from paylog where paydate <= a.paydate) from paylog a
)
,t2 as
(
select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from costlog where costdate <= a.costdate) from costlog a
)
,t3 as
(
select a.*,bid1=b.id
from t1 a
cross apply (select top (1) * from t2 where sumgold >= a.sumgold order by sumgold) b
)
,t4 as
(
select a.*,bid2=isnull(b.bid1,1) from t3 a left join t3 b on a.id = b.id + 1
)
,t5 as
(
select paygold=a.gold,a.paydate,a.des,costgold=b.gold,b.costdate,details=
case
when a.sumgold>=b.sumgold then
case when a.gold-a.sumgold+b.sumgold >= b.gold then b.gold else a.gold-a.sumgold+b.sumgold end
else
case when b.gold-b.sumgold+a.sumgold >= a.gold then a.gold else b.gold-b.sumgold+a.sumgold end
end
from t4 a join t2 b on b.id between a.bid2 and a.bid1
)
select * from t5 where details > 0--3:create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int)
godeclare my_cursor cursor scroll
for
select * from paylog
open my_cursor
declare @gold int
declare @paydate datetime
declare @des nvarchar(10)
set @gold = 0
fetch next from my_cursor into @gold,@paydate,@des
while (@@fetch_status = 0)
begin
declare next_cursor cursor scroll
for
select * from costlog
open next_cursor
declare @go int
declare @costdate datetime
declare @details int
declare @gocun int
declare @decun int
set @decun = 0
set @go = 0
set @details = 0
set @gocun = @gold
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
while(@@fetch_status = 0)
begin
if(@gocun = 0)
begin
set @details = @gold
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
fetch next from next_cursor into @go,@costdate
set @gocun = @gold - @go
set @decun = 0
end
if(@gocun > 0)
begin
set @details = @go - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
set @decun = 0
end
if(@gocun < 0)
begin
set @details = @go + @gocun - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
set @gocun = @gold + @gocun
set @decun = @details
end
end
close next_cursor
deallocate next_cursor
end
close my_cursor
deallocate my_cursor
select * from cun
在软件实现,有两种做法:
第一种,用批次法,这种对开发方式比较简单,就是所有进项都有一个批号,在销售的时候按批号日期排序来选择。如进仓: 批号 日期 商品 数量 单价
201101 01-02 A 20 2
201101 01-02 B 30 3
201102 01-03 A 40 2.5然后在销售的时候从进仓里选择,按批号来选择,同一个商品A,201101批的进价是2,201102批的进价是2.5,销售的时候按先进先出的原则,先选择201101批,销售完后再选择201102批。第二种,用软件方法来计算,如进仓:日期 商品 数量 单价 已销售数量 销售单价
01-02 A 20 2 10 3.5
01-02 B 30 3 20 5
01-03 A 40 2.5已销售数量从销售单据里反填进入,在反填的时候,根据日期排序,一行一行进行反填,一定要注意数量,只要相等了,就下一行数据反填已销售数量和销售单价。