declare @YCD table (cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)
insert into @YCD
select 'YCD001','2012-01-01','01','A',200 union all
select 'YCD001','2012-01-02','01','A',800 union all
select 'YCD001','2012-01-05','01','B',1000 union all
select 'YCD002','2012-01-03','01','A',2000 union all
select 'YCD003','2012-01-04','02','A',3000

declare @FHD table (dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500

select * from @YCD UNION all
select NULL,NULL,* from @FHD ORDER BY cp
/*
cno    ddate                   dept cp   qty
------ ----------------------- ---- ---- -----------
YCD002 2012-01-03 00:00:00.000 01   A    2000
YCD003 2012-01-04 00:00:00.000 02   A    3000
NULL   NULL                    01   A    3500
YCD001 2012-01-01 00:00:00.000 01   A    200
YCD001 2012-01-02 00:00:00.000 01   A    800
YCD001 2012-01-05 00:00:00.000 01   B    1000
NULL   NULL                    02   B    500
*/

--貌似group +sum  然后联查做差即可。

------解决方案--------------------

SQL code

declare @YCD table (id int identity(1,1),cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)
insert into @YCD
select 'YCD001','2012-01-01','01','A',200 union all
select 'YCD001','2012-01-02','01','A',800 union all
select 'YCD001','2012-01-05','01','B',1000 union all
select 'YCD002','2012-01-03','01','A',2000 union all
select 'YCD003','2012-01-04','02','A',3000

declare @FHD table (id int identity(1,1),dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500

select a.*,0 as px,
        (case when b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<=a.id)>=0
              then a.qty
              else case when b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<a.id)>=0
                   then b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<a.id) else 0 end end) sqty
from @YCD a left join @FHD b on a.cp = b.cp and a.dept = b.dept
union all
select null,null,null,a.dept,a.cp,a.qty,1,a.qty-isnull(b.qty,0)
from @FHD a left join (select dept,cp,sum(qty) qty from @YCD group by dept,cp) b
    on a.cp = b.cp and a.dept = b.dept
order by dept,px

/************************

id          cno    ddate                   dept cp   qty         px          sqty
----------- ------ ----------------------- ---- ---- ----------- ----