按日期范围查询ID断号情况,并进行聚合求和

if object_id ( ' tempdb..#T ' ) is not null
drop table #T

create table #T(ID int ,SaleDate datetime ,Price int )
insert into #T select 1 , ' 2008-1-1 ' , 40
insert into #T select 2 , 50
insert into #T select 3 , ' 2008-1-2 ' , 24
insert into #T select 6 , 10
insert into #T select 7 , ' 2008-1-3 ' , 12
insert into #T select 8 , 20
go

create proc p_test
(
@begin_date datetime ,
@end_date datetime
)
as
select ID = ltrim (ID) +
case when exists ( select 1 from #T
where SaleDate between @begin_date and @end_date
and ID = a.ID + 1 ) then ' - ' +
ltrim (( select min (ID) from #Tb
where SaleDate between @begin_date and @end_date and ID >= a.ID
and not exists ( select 1 from #T
where SaleDate between @begin_date and @end_date
and ID = b.ID + 1 )
)) else '' end ,

Total_Price = ( select sum (Price) from #Tb
where SaleDate between @begin_date and @end_date
and ID between a.ID and
( select min (ID) from #Tb
where SaleDate between @begin_date and @end_date and ID >= a.ID
and not exists ( select 1 from #T
where SaleDate between @begin_date and @end_date
and ID = b.ID + 1 )
)
)

from #Ta
where SaleDate between @begin_date and @end_date
and not exists
( select 1 from #T where ID = a.ID - 1 and SaleDate between @begin_date and @end_date )


go
exec p_test ' 2008-01-01 ' , ' 2008-01-03 '
exec p_test ' 2008-01-01 ' , ' 2008-01-02 '
go
drop table #T
drop proc p_test

/**/ /*
IDTotal_Price
------------------------------------
1-3114
6-842

（所影响的行数为2行）

IDTotal_Price
------------------------------------
1-3114
610

（所影响的行数为2行）
*/