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