求SQL算法！！

[appleller]

号码段使用情况计算
--入库单据号码情况：入库日期，号码开始号，号码结束号
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

[中国风]

select '2012-12-7',10050,16000 union all --改改测试数据

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',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 
	b.ddate,
	b.outendno+1 as StartNr,
	a.inendno as EndNr
from #tb01 as a 
	inner join #tb02 as b on b.[outendno]>a.[instartno] and a.[inendno]>b.[outstartno] and b.[outendno]<a.[inendno]
),b as
(
select b.ddate,
	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]
)
select 
	a.StartNr,
	case when b.ddate 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)

/*
StartNr	EndNr
10000	10049
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)

/*
StartNr	EndNr
5000	8000
10000	10049
16001	20000
70001	79999
85001	90000
*/

[appleller]

版主出现了！ 此贴有救了！ 初步测试完全正确，拜谢了！！

[appleller]

CTE用法看不太懂。 再问一下： 此结果无法再SELECT查询出来呢？ 如果要把此结果插入表中怎么写？ insert into tablea select * from (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) a 这样不行呢？

[撸大湿]

;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