SELECT
MOCTA.TA001+'-'+MOCTA.TA002 '单别/单号',
MOCTA.TA006 品号,
MOCTA.TA034 品名,
MOCTA.TA035 规格,
MOCTA.TA015 预计产量,
ISNULL((SELECT CONVERT(varchar(100),CAST(MOCTC.TC003 AS datetime),23) FROM MOCTC 
WHERE MOCTC.TC001=MOCTE.TE001 AND MOCTC.TC002=MOCTE.TE002),'') 发外日期,
ISNULL((CAST(MOCTE.TE005 as VARCHAR(50))),'') 发外数量,
ISNULL((CAST(MOCTI.TI007 as VARCHAR(50))),'') 进货数量
FROM MOCTA
LEFT JOIN MOCTE ON MOCTE.TE011=MOCTA.TA001 AND MOCTE.TE012=MOCTA.TA002 AND MOCTE.TE019='Y'
LEFT JOIN MOCTI ON MOCTI.TI013=MOCTA.TA001 AND MOCTI.TI014=MOCTA.TA002 AND MOCTI.TI037='Y'
WHERE MOCTA.TA013='Y' AND MOCTA.TA001 IN('513','522') AND MOCTA.TA002='120900527'
ORDER BY MOCTA.TA001+MOCTA.TA002 asc

--构建测试数据
create table MOCTA(TA001 varchar(10),TA002 varchar(10),TA006 varchar(10),TA034 varchar(10),TA035 varchar(10),TA015 int,TA013  varchar(10))
insert into MOCTA select '513','120900527','A01','desk','200*100*20',100,'Y'
create table MOCTC(TC002 int,TC003 varchar(20),TC001 int )
insert into MOCTC select 1,'2012-09-13',1
union select 2,'2012-09-15',2
union select 3,'2012-09-18',3
create table MOCTE(TE011 varchar(10),TE012 varchar(10),TE005 int,TE019  varchar(10),TE001 int,TE002 int)
insert into MOCTE select '513','120900527',30,'Y',1,1
union select '513','120900527',50,'Y',2,2
union select '513','120900527',20,'Y',3,3
create table MOCTI(TI013 varchar(10),TI014 varchar(10),TI007 int,TI037  varchar(10))
insert into MOCTI select '513','120900527',35,'Y'


--解决方案
;with CET1 as(SELECT
a.TA001+'-'+a.TA002 [单别/单号],
a.TA006 品号,
a.TA034 品名,
a.TA035 规格,
a.TA015 预计产量,
ISNULL(CONVERT(varchar(100),CAST(d.TC003 AS datetime),23),'') 发外日期,
ISNULL(b.TE005 ,0) 发外数量,
ISNULL(c.TI007 ,0) 进货数量
FROM MOCTA a
LEFT JOIN MOCTE b ON b.TE011=a.TA001 AND b.TE012=a.TA002 AND b.TE019='Y'
LEFT JOIN MOCTI c ON c.TI013=a.TA001 AND c.TI014=a.TA002 AND c.TI037='Y'
left join MOCTC d on d.TC001=b.TE001 AND d.TC002=b.TE002
WHERE a.TA013='Y' AND a.TA001 IN('513','522') AND a.TA002='120900527'
--ORDER BY a.TA001+a.TA002,发外日期 asc
)select [单别/单号],品号,品名,规格,预计产量,发外日期,发外数量,
case when 进货数量 > 发外数量sum  then 发外数量 
                    else case when 进货数量 > 发外数量sum - 发外数量 then  进货数量 - 发外数量sum + 发外数量
                                                                else 0 end 
                    end 进货数量
from CET1 a cross apply 
(select sum(发外数量) 发外数量sum from CET1 b where a.[单别/单号]= b.[单别/单号] and a.品号 = b.品号 and b.发外日期 <= a.发外日期)b
ORDER BY [单别/单号],发外日期
/*
单别/单号             品号       品名       规格       预计产量    发外日期            发外数量    进货数量
--------------------- ---------- ---------- ---------- ----------- --------------------------------------------
513-120900527         A01        desk