求SQL算法！！
号码段使用情况计算
--入库单据号码情况：入库日期，号码开始号，号码结束号
select ddate,instartno,inendno from tb01
2012-12-6/10000/20000
2012-12-8/60000/90000

--领用单据号码情况：领用日期，号码开始号，号码结束号
select ddate,outstartno,outendno from tb02
2012-12-7/10000/16000
2012-12-12/60000/70000
2012-12-25/80000/85000


SQL查询截止某一天的库存情况，结存开始号，结存结束号
例如：2012-12-31剩余库存报表
balstartno,balendno
16001/20000
70001/79999
85001/90000


[解决办法]
改改
改一下入库测试数据，加上日期条件

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#tb01') is null
drop table #tb01
Go
Create table #tb01([ddate] Datetime,[instartno] int,[inendno] int)
Insert #tb01
select '2012-12-6',5000,8000 union all
select '2012-12-6',10000,20000 union all
select '2012-12-8',60000,90000
Go

 
if not object_id(N'Tempdb..#tb02') is null
drop table #tb02
Go
Create table #tb02([ddate] Datetime,[outstartno] int,[outendno] int)
Insert #tb02
select '2012-12-7',10050,16000 union all--改改测试数据
select '2012-12-12',60000,70000 union all
select '2012-12-25',80000,85000
Go
;with a
as
(
select 
case when b.[outendno] is not null then b.outendno+1 else a.instartno end as  StartNr,
case when b.[outendno] is not null then a.inendno else a.inendno end as  EndNr
from #tb01 as a 
left join #tb02 as b on b.[outendno]>a.[instartno] and a.[inendno]>b.[outstartno] and b.[outendno]<a.[inendno] and b.ddate<='2012-12-31' 
where a.ddate<='2012-12-31' 
),b as
(
select 
a.[instartno] as StartNr,
b.[outstartno]-1 as EndNr
from #tb01 as a 
inner join #tb02 as b on b.[outendno]>a.[instartno] and a.[inendno]>b.[outstartno] and b.[outstartno]>a.[instartno]
where a.ddate<='2012-12-31' and b.ddate<='2012-12-31' 
)
select 
a.StartNr,
case when b.EndNr is not null then b.EndNr else  a.EndNr end as EndNr
from a
left join b on a.EndNr>b.StartNr and b.EndNr>a.StartNr
union 
select 
StartNr,EndNr
from b
where not exists(select 1 from a where a.EndNr>b.StartNr and b.EndNr>a.StartNr)

/*
StartNrEndNr
50008000
1000010049
1600120000
7000179999
8500190000
*/

[解决办法]


;with?a
as
(
select?
case?when?b.[outendno]?is?not?null?then?b.outendno+1?else?a.instartno?end?as??StartNr,
case?when?b.[outendno]?is?not?null?then?a.inendno?else?a.inendno?end?as??EndNr

from?#tb01?as?a?
left?join?#tb02?as?b?on?b.[outendno]>a.[instartno]?and?a.[inendno]>b.[outstartno]?and?b.[outendno]<a.[inendno]?and?b.ddate<='2012-12-31'?
where?a.ddate<='2012-12-31'?
),b?as
(
select?
a.[instartno]?as?StartNr,
b.[outstartno]-1?as?EndNr
from?#tb01?as?a?
inner?join?#tb02?as?b?on?b.[outendno]>a.[instartno]?and?a.[inendno]>b.[outstartno]?and?b.[outstartno]>a.[instartno]
where?a.ddate<='2012-12-31'?and?b.ddate<='2012-12-31'?
)
--加这里
insert into tableA
select?
a.StartNr,
case?when?b.EndNr?is?not?null?then?b.EndNr?else??a.EndNr?end?as?EndNr
from?a
left?join?b?on?a.EndNr>b.StartNr?and?b.EndNr>a.StartNr
union?
select?
StartNr,EndNr
from?b
where?not?exists(select?1?from?a?where?a.EndNr>b.StartNr?and?b.EndNr>a.StartN