select
a.itemcode as 物料编号,
a.quantity as 交货数,
isnull(b.quantity,0) as 退货数,
a.quantity-isnull(b.quantity,0) as 实际售出,
a.docdate as 交货日期
from
(select itemcode,sum(quantity) as quantity,docdate from dln1 group by itemcode,docdate) a
left join
(select itemcode,sum(quantity) as quantity,docdate from rdn1 group by itemcode,docdate) b
on
a.itemcode=b.itemcode and a.docdate=b.docdate
select a.itemcode,a.quantity ,isnull(b.quantity,0),
a.quantity -isnull(b.quantity,0),a.docdate
from dln1 a left join rdn1 b
on a.itemcode=b.itemcode
where a.docdate =b.docdate
a.itemcode as 物料编号,
sum(a.quantity) as 交货数,
isnull(sum(b.quantity),0) as 退货数,
sum(a.quantity-isnull(b.quantity,0)) as 实际售出,
convert(varchar(10),a.docdate,120) as 交货日期
from dln1 a
left join rdn1 b
on a.itemcode=b.itemcode and datediff(day,a.交货日期,b.退货日期)=0
?
itemcode varchar(32), quantity int, docdate datetime)
insert into @dln1 select 'A01', 10 ,' 2009-03-26'
insert into @dln1 select 'A02', 5 ,'2009-03-26 '
insert into @dln1 select 'A01', 3 ,'2009-03-26 'declare @rdn1 table (
itemcode varchar(32), quantity int, docdate datetime)insert into @rdn1 select 'A01', 5 ,'2009-03-26 'select
a.itemcode as 物料编号,
a.quantity as 交货数,
isnull(b.quantity,0) as 退货数,
a.quantity-isnull(b.quantity,0) as 实际售出,
a.docdate as 交货日期
from
(select itemcode,sum(quantity) as quantity,docdate from @dln1 group by itemcode,docdate) a
left join
(select itemcode,sum(quantity) as quantity,docdate from @rdn1 group by itemcode,docdate) b
on
a.itemcode=b.itemcode and a.docdate=b.docdate/*
物料编号 交货数 退货数 实际售出 交货日期
-------------------------------- ----------- ----------- ----------- -----------------------
A01 13 5 8 2009-03-26 00:00:00.000
A02 5 0 5 2009-03-26 00:00:00.000
*/
insert into dln1 values('AO1',10,'2009-03-26')
insert into dln1 values('AO2',5,'2009-03-26')
insert into dln1 values('AO1',3,'2009-03-26')create table rdn1(itemcode varchar(8),quantity int,docdate datetime)
insert into rdn1 values('AO1',5,'2009-03-26')
select
a.itemcode as 物料编号,
a.quantity as 交货数,
isnull(b.quantity,0) as 退货数,
a.quantity-isnull(b.quantity,0) as 实际售出,
a.docdate as 交货日期
from (select itemcode,sum(quantity) quantity,docdate from dln1 group by itemcode,docdate) a left join rdn1 as b
on a.itemcode=b.itemcode
(select a.itemcode 物料编号,sum(quantity) 交货数, docdate 交货日期
from dln1 a
group by a.itemcode,docdate) a
left join rdn1
on a.itemcode=b.itemcode