求一高效的算法，关于时间段 数据汇总

[michael_zy]

假设有如下两张表：
表A:
id type begin_date end_date count
---------------------------------------------
1 A 2007-5-12 2007-5-14 30
2 A 2007-5-11 2007-5-13 20
3 B 2007-5-12 2007-5-15 50
4 B 2007-5-13 2007-5-14 30

表示 从begin_date到end_date的时间段内，每天都会有30数量的A，其它行都是相同的意思

表B:（消耗表)
id type use_date count
------------------------------
1 A 2007-5-11 15
2 A 2007-5-12 15
3 B 2007-5-12 20
4 B 2007-5-13 30

表A和表B的id没有关系，现在要根据某一时间段，查询剩余数，比如：2007-5-11到2007-5-14的结余数量：

得到余下结果:
id type 2007-5-11 2007-5-12 2007-5-13 2007-5-14
1 A 5 35 50 30
2 B 0 30 50 80

其中2007-5-12,5-13,5-14是根据条件动态生成。。。。

怎么比较高效的实现上述功能。。。我想到的需要循环好几次，实在是够理想，请朋友们一起想想，或者，通过修改表结构，达到相同的目的

谢谢

[dsdpz]

表1得明确一下，产品的TYPE与期间的关系啊，不太懂意思，如果要得到余数，公式肯定只有一个：总数－消耗数＝余数，表的定义应该根据工作流程、实际需要来写啊，单单两张表没有流程，不太好发表意见啊

[free_pop2k]

改表的结构吧,改成
---------------------------
id type date count InOut

其中 date就是有进料或有消耗料的日期
InOut 就是当为in 时表示进料,当为Out时表示消耗料

[chuifengde]

create table aaab(id int, type char(1), begin_date smalldatetime, end_date smalldatetime, count int)
insert aaab select 1 ,'A', '2007-5-12', '2007-5-14', 30
union all select 2 ,'A', '2007-5-11', '2007-5-13', 20
union all select 3 ,'B', '2007-5-12', '2007-5-15', 50
union all select 4 ,'B', '2007-5-13', '2007-5-14', 30

go
create table aaac(id int, type char(1), use_date smalldatetime, count int)
insert aaac select 1 ,'A', '2007-5-11', 15
union all select 2 ,'A', '2007-5-12', 15
union all select 3 ,'B', '2007-5-12', 20
union all select 4 ,'B', '2007-5-13', 30

go

create proc testPPPP
@x smalldatetime,
@y smalldatetime
as

declare @a table(a int identity(1,1),type char(1),dat smalldatetime,count int)
declare @type char(1),@be smalldatetime,@ed smalldatetime,@count int,@i int

declare cur cursor for
select type,begin_date,end_date,count from aaab
open cur

fetch next from cur into @type,@be,@ed,@count
while @@fetch_status=0
begin
set @i=0
while @i<=datediff(day,@be,@ed)
begin
insert @a select @type,dateadd(day,@i,@be),@count
set @i=@i+1
end
fetch next from cur into @type,@be,@ed,@count
end

close cur
deallocate cur

select a.type,dat,cou=sum(isnull(a.count,0)-isnull(b.count,0)) into tmpHJ from (select type,dat,sum(count) count from @a group by type,dat) a left join aaac b on a.type=b.type and datediff(day,dat,use_date)=0 group by a.type,dat
--
declare @v varchar(3000)

select @v=coalesce(@v+',','')+' sum(case when datediff(day,dat,'''+convert(varchar(10),dat,120)+''')=0 then cou else 0 end) ['+convert(varchar(10),dat,120)+']' from tmpHJ where dat between @x and @y group by dat
select @v='select type,'+@v+' from tmpHJ group by type'
exec(@v)
--
drop table tmpHJ

go

exec testPPPP '2007-5-11','2007-5-14'

--result
/*
type 2007-05-11 2007-05-12 2007-05-13 2007-05-14
---- ----------- ----------- ----------- -----------
A 5 35 50 30
B 0 30 50 80

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

[free_pop2k]

create proc dbo.proc_test(--以改了表结构为例
create table t(id int,type varchar(2),[date] datetime,[count] int,InOut varchar(3))

insert into t
select 1,'aa','2007-01-01',100,'in'
union all
select 2,'bb','2007-01-01',200,'in'
union all
select 3,'aa','2007-01-01',50,'out'

alter proc dbo.proc_test(@StartDate datetime,@EndDate datetime)
as
begin
set nocount on
declare @Dt datetime
declare @SQL varchar(4000)
select @Dt=@StartDate
begin tran
select @SQL='select distinct type '
while @Dt<=@EndDate
begin
select @sql=@sql + ',' + '(select sum(case InOut when ''in'' then [count] else -[count] end ) from t where type=a.type and [date]<=' + '''' + convert(varchar(20),@Dt,120) + ''' group by type ' + ') as ' + '[' +left(convert(varchar(20),@Dt,120),10) + ']'
select @Dt=dateadd(dd,1,@Dt)
end
select @sql=@sql + ' from t a'
if @@error=0
commit tran
else
rollback tran

--@SQL
exec (@sql)
end

dbo.proc_test '2007-01-01','2007-01-04'

drop table t
drop proc proc_test@StartDate datetime,@endDate datetime)

[free_pop2k]

--以改了表结构为例

create table t(id int,type varchar(2),[date] datetime,[count] int,InOut varchar(3))

insert into t
select 1,'aa','2007-01-01',100,'in'
union all
select 2,'bb','2007-01-01',200,'in'
union all
select 3,'aa','2007-01-01',50,'out'

create proc dbo.proc_test(@StartDate datetime,@EndDate datetime)
as
begin
set nocount on
declare @Dt datetime
declare @SQL varchar(4000)
select @Dt=@StartDate
begin tran
select @SQL='select distinct type '
while @Dt<=@EndDate
begin
select @sql=@sql + ',' + '(select sum(case InOut when ''in'' then [count] else -[count] end ) from t where type=a.type and [date]<=' + '''' + convert(varchar(20),@Dt,120) + ''' group by type ' + ') as ' + '[' +left(convert(varchar(20),@Dt,120),10) + ']'
select @Dt=dateadd(dd,1,@Dt)
end
select @sql=@sql + ' from t a'
if @@error=0
commit tran
else
rollback tran

--@SQL
exec (@sql)
end

dbo.proc_test '2007-01-01','2007-01-04'

drop table t
drop proc proc_test

[Andy-W]

借用楼上的表结构数据，测试我的方法，很简单的了：

create table TableA(id int, type char(1), begin_date smalldatetime, end_date smalldatetime, [count] int)
insert TableA select 1 ,'A', '2007-5-12', '2007-5-14', 30
union all select 2 ,'A', '2007-5-11', '2007-5-13', 20
union all select 3 ,'B', '2007-5-12', '2007-5-15', 50
union all select 4 ,'B', '2007-5-13', '2007-5-14', 30
go
create table TableB(id int, type char(1), use_date smalldatetime, [count] int)
insert TableB select 1 ,'A', '2007-5-11', 15
union all select 2 ,'A', '2007-5-12', 15
union all select 3 ,'B', '2007-5-12', 20
union all select 4 ,'B', '2007-5-13', 30
go

DECLARE @BeginDate smalldatetime --开始日期
DECLARE @EndDate smalldatetime --结束日期
DECLARE @TmpDate smalldatetime
DECLARE @EXECUTE_SQL nvarchar(4000) --
SELECT @BeginDate='2007-5-11'
,@EndDate='2007-5-14'
,@TmpDate=@BeginDate
,@EXECUTE_SQL='SELECT type'

CREATE TABLE #T(TDate smalldatetime) --构造临时表，用于分类统计，和构造行列转换语句
WHILE @TmpDate<=@EndDate
BEGIN
INSERT INTO #T SELECT @TmpDate
SELECT @EXECUTE_SQL=@EXECUTE_SQL+',SUM(CASE TDATE WHEN '''+CONVERT(nchar(10),@TmpDate,120)+''' THEN [count] ELSE 0 END) AS ['+CONVERT(nchar(10),@TmpDate,120)+']'
,@TmpDate=DATEADD(day,1,@TmpDate)

END
SET @EXECUTE_SQL=@EXECUTE_SQL+CHAR(10)+'FROM #T1 GROUP BY type'

--没有行列转换前统计，插入表#T1
SELECT type,TDate,SUM([count]) AS [count] INTO #T1
FROM (
SELECT type,TDate,[count] FROM TableA CROSS JOIN #T WHERE (begin_date BETWEEN @BeginDate AND @EndDate
OR end_date BETWEEN @BeginDate AND @EndDate)
AND TDate BETWEEN begin_date AND end_date
UNION ALL SELECT type,use_date,-[count] FROM TableB WHERE use_date BETWEEN @BeginDate AND @EndDate
) AS A
GROUP BY type,TDate
ORDER BY type,TDate
EXECUTE( @EXECUTE_SQL)
DROP TABLE TableA,TableB,#T,#T1
go

/*
type 2007-05-11 2007-05-12 2007-05-13 2007-05-14
---------------------------------------------------------------------------
A 5 35 50 30
B 0 30 50 80
*/

[Andy-W]

以上方法没有使用到游标，只是使用到2张临时表就可以拷定，其实也可以使用1个临时表就可以，只不过为了方便更好的了解计算方法，把分类统计过程独立出来。
这方法虽然语句有点长吧，但我相信比使用游标更快。呵呵

[playwarcraft]

把第一張表改成第二張表一樣的結構,就直接可以查詢了

[michael_zy]

改表结构，不太符合现实中的业务，因为，这个预定的时间断可能是 从今年到明年 ，对用户来说，是以条操作，难道要存365＋的记录。。。不能这么改

[michael_zy]

谢了，大家，树上的鸟些的和我的想法一样，但是 循环了几次，觉得不太好

[free_pop2k]

我认为这个数据库设得不合理.当然现在改有点不实际