------预测单
create table #yc(cp varchar(10),startdate datetime,enddate datetime ,qty int)
insert into #yc 
select 'A','2012-05-01','2012-05-07',100
union 
select 'B','2012-05-02','2012-05-05',200
union 
select 'C','2012-05-01','2012-05-10',300
union 
select 'D','2012-05-01','2012-05-08',400
union 
select 'E','2012-05-01','2012-05-09',500
-----发货单
create table #fh(cp varchar(10),fhdate datetime ,qty int)
insert into #fh
select 'B','2012-05-04',50
union
select 'A','2012-05-08',30
union
select 'C','2012-05-10',100
union
select 'D','2012-05-12',200
union
select 'F','2012-05-15',100

---------得到下面结果。说明：发货单发货日期在预测单时间范围内，就并成一行，不成，就单独成一行。




cp         startdate               enddate                 fhdate                  ycqty       fhqty
---------- ----------------------- ----------------------- ----------------------- ----------- -----------
A          NULL                    NULL                    2012-05-08 00:00:00.000 NULL        30
A          2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 NULL                    100         NULL
B          2012-05-02 00:00:00.000 2012-05-05 00:00:00.000 2012-05-04 00:00:00.000 100         50
C          2012-05-01 00:00:00.000 2012-05-10 00:00:00.000 2012-05-10 00:00:00.000 300         100
D          NULL                    NULL                    2012-05-12 00:00:00.000 NULL        200
D          2012-05-01 00:00:00.000 2012-05-08 00:00:00.000 NULL                    400         NULL
E          2012-05-01 00:00:00.000 2012-05-09 00:00:00.000 NULL                    500         NULL
F          NULL                    NULL                    2012-05-15 00:00:00.000 NULL        100

select cp, null as startdate , null as   enddate ,fhdate  ,null  as  ycqty,fhqty from #fh
union all
select  cp,startdate ,  enddate ,null as fhdate  ,ycqty,null  as  fhqty from #fh
order by cp

------解决方案--------------------
木有看懂意思。

------解决方案--------------------
full join ?


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

SQL code
select cp=isnull(s.cp,t.cp),startdate,enddate,fhdate,ycqty=s.qty,t.qty 
from #yc s full join #fh  t on s.cp=t.cp and fhdate between startdate and enddate
order by isnull(s.cp,t.cp)


A    2012-05-01 00:00:00.000    2012-05-07 00:00:00.000    NULL    100    NULL
A    NULL    NULL    2012-05-08 00:00:00.000    NULL    30
B    2012-05-02 00:00:00.000    2012-05-05 00:00:00.000    2012-05-04 00:00:00.000    200    50
C    2012-05-01 00:00:00.000    2012-05-10 00:00:00.000    2012-05-10 00:00:00.000    300    100
D    2012-05-01 00:00:00.000    2012-05-08 00:00:00.000    NULL    400    NULL
D    NULL    NULL    2012-05-12 00:00:00.000    NULL    200
E    2012-05-01 00:00:00.000    2012-05-09 00:00:00.000    NULL    500    NULL
F    NULL    NULL    2012-05-15 00:00:00.000    NULL    100

------解决方案--------------------
SQL code
select a.cp,a.startdate,a.enddate,fhdate=(select fhdate from #fh where a.cp=cp and  fhdate>=a.startdate and fhdate<=a.enddate),a.qty as ycqty,fhqty=(select qty from #fh where a.cp=cp and  fhdate>=a.startdate and fhdate<=a.enddate) from #yc a 

union 
select a.cp,startdate=(select startdate from #yc where a.cp=cp and  a.fhdate>=startdate and a.fhdate<=enddate),enddate=(select enddate from #yc where a.cp=cp and  a.fhdate>=startdate and a.fhdate<=enddate),fhdate,ycqty=(select qty from #yc where a.cp=cp and  a.fhdate>=startdate and a.fhdate<=end