关于先进先出成本计算1

create table #t
(
pid int,
pname varchar(10),
jsum int,
jpri int,
jdate datetime
)

create table #p
(
pid int,
pname varchar(10),
xnum int,
xdate smalldatetime
)

insert into #t select    1,         'aa',         100,          10,        '2007-1-1'
insert into #t select    1,         'aa',          50,          20,        '2007-1-15'
insert into #t select    1,         'aa',         100,          22,        '2007-1-20'

insert into #p select    1,         'aa',         50,    '2007-1-2'
insert into #p select    1,         'aa',         60,   '2007-1-16'
insert into #p select    1,         'aa',         10,   '2007-1-21'
insert into #p select    1,         'aa',         40,   '2007-1-22'

--语句
select pp.pid,pp.pname,pp.xnum,
cb = sum(case when qxnum >= qjnum and xxnum <= xjnum then xnum * jpri
           when qxnum >= qjnum and xxnum >= xjnum then (xjnum - qxnum) * jpri
           when qxnum <= qjnum and xxnum <= xjnum then (xxnum - qjnum) * jpri
      end),
pp.xdate
from
(select pid,pname,jsum,
qjnum = (select isnull(sum(jsum),0) from #t where pid = t.pid and jdate < t.jdate),
xjnum = (select sum(jsum) from #t where pid = t.pid and jdate <= t.jdate),
       jpri,jdate
from #t t) tt,
(select pid,pname,xnum,xdate,
qxnum = (select isnull(sum(xnum),0) from #p where pid = p.pid and xdate < p.xdate),
xxnum = (select sum(xnum) from #p where pid = p.pid and xdate <= p.xdate)
from #p p ) pp
where qxnum between qjnum and xjnum or xxnum between qjnum and xjnum
group by pp.pid,pp.pname,pp.xnum,pp.xdate
order by xdate