日期:2014-05-18  浏览次数:20474 次

求连续最大天数对应的起始和终止日期?
测试数据的生成,连续最大天数的求解,这里给出:(以1个月为例)

/*
create table LoginTable (name varchar(10),logindate smalldatetime)

declare @i int
set @i=0
while @i<400
begin
  insert into LoginTable values(char(abs(CHECKSUM(newid()))%26+65),dateadd(dd,abs(CHECKSUM(newid()))%31,'2011-01-01'))
  select @i=@i+1
end

drop table #temp
select distinct name,logindate 
into #temp
from LoginTable

truncate table LoginTable

insert into LoginTable select * from #temp
select * from LoginTable order by name,logindate 
*/

if object_id('fcon_max') is not null
drop function fcon_max
go

--由连续天数获得一个参数@n
--function作用:由@n得到连续1的最大数目
create function fcon_max(@n bigint)
returns int 
as 
begin 
declare @max int,@t int,@j int
select @max=0,@t=0
while (@n>0)
begin
  if (@n%2=0)
  begin
  if (@max<@t) set @max=@t
  set @t=0
  end
  else set @t=@t+1
  set @n=@n/2
end
select @j=case when @t<@max then @max else @t end
return @j
end
go

--选取前100位name的数据
select top 100 name,dbo.fcon_max(loginday) as Logindays
from
(  
SELECT name,sum(power(cast(2 as bigint),day(logindate)-1)) as loginday 
FROM logintable group by name
) final


问题:每个name的最大连续天数logindays,由上述程序已经知道了。
  按这个思路,怎么得到每个name的logindays所对应的起始和终止日期:strat_day和end_day?
  即最终形式的表为:(name,strat_day,end_day,logindays)
  请各位畅言,有好的想法也可以。。。





------解决方案--------------------
SQL code

--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([proname] varchar(2),[qty] int,[vtype] int,[optime] datetime)
insert [tbl]
select 'AA',-10,30,'2012-01-09' union all
select 'AA',21,31,'2012-02-14' union all
select 'AA',12,31,'2012-02-11' union all
select 'AA',-2,30,'2012-03-11' union all
select 'BB',-1,30,'2012-02-12' union all
select 'BB',90,31,'2012-02-11' union all
select 'BB',90,31,'2012-03-17' union all
select 'BB',-6,30,'2012-02-19'

select [proname],SUM(case when  [vtype] = 31 THEN [qty] ELSE 0 END) as xsqty,
SUM(CASE WHEN [vtype] = 30 THEN [qty] ELSE 0 END) AS xtqty,
(select convert(varchar(10),max([optime]),120) from tbl b where a.proname=b.proname and [vtype] = 31) as lastxstime ,
(select convert(varchar(10),max([optime]),120) from tbl b where a.proname=b.proname and [vtype] = 30) as lastxstime  
from tbl a
group by [proname]
/*
proname    xsqty    xtqty    lastxstime    lastxstime
AA    33    -12    2012-02-14    2012-03-11
BB    180    -7    2012-03-17    2012-02-19
*/

你看看这个例子对你有没有帮助
我有点不怎么明白你的问题

------解决方案--------------------
SQL code

-> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([name] varchar(2),[logindate] date)
insert [tbl]
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24'

with t as(
select [name],[logindate],(select min(b.[logindate]) from tbl b 
where b.[logindate]>=a.[logindate] and b.name=a.name
and not exists (select * from tbl c
where c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grp
from tbl a
),m
as(
select [name],MIN([logindate]) as startdate,MAX(grp) as enddate
from t group by grp,name
)
select *,(DATEDIFF(DD,startdate,enddat