27,579
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from sysobjects where xtype='fn' and name='days')
drop function days
go
create function days(
@date datetime,
@days_start int,
@days_end int
)
returns varchar(100)
as
begin
declare @t table(开户类型 varchar(10),开始时间 datetime,结束时间 datetime)
insert into @t select '类型1','2010-01-01','2010-10-15'
union all select '类型2','2010-10-20','2010-12-15'
union all select '类型3','2010-12-12','2011-01-01'
declare @start datetime,@end datetime
set @start=@date-@days_end
set @end=@date-@days_start
if(not exists(select 1 from @t where @start between 开始时间 and 结束时间))
set @start=(select MIN(开始时间) from @t where 开始时间>@start)
if(not exists(select 1 from @t where @end between 开始时间 and 结束时间))
set @end=(select MAX(结束时间) from @t where 结束时间<@end)
declare @i int,@counts int
set @i=1;
set @counts=0
while (@i<convert(int,@end-@start))
begin
if not exists(select 1 from @t where (@start+@i) between 开始时间 and 结束时间)
set @counts=@counts+1
set @i=@i+1
end
return convert(varchar,convert(int,@end-@start-@counts))+'天 '+
'逻辑说明('+convert(varchar,@start,102)+'到'+
convert(varchar,@end,102)+')'
end
go
select '0-30天使用时间:',dbo.days('2011-1-1',0,30)
select '30-60天使用时间:',dbo.days('2011-01-01',30,60)
select '60-90天使用时间:',dbo.days('2011-1-1',60,90)
0-30天使用时间: 30天 逻辑说明(2010.12.02到2011.01.01)
30-60天使用时间: 30天 逻辑说明(2010.11.02到2010.12.02)
60-90天使用时间: 26天 逻辑说明(2010.10.03到2010.11.02)
declare @T table (开户类型 varchar(5),开始时间 datetime,结束时间 datetime)
insert into @T
select '类型1','2010-01-01','2010-10-15' union all
select '类型2','2010-10-20','2010-12-15' union all
select '类型3','2010-12-12','2011-01-01'
--假设今天是 2011-01-01
declare @i datetime set @i='2011-01-01'
select c+ltrim(case when c1>30 then 30 else c1 end)+'天 逻辑说明('+d1+'到'+d2+')' from
(
select '0-30天使用时间:' as c,sum(dbo.generateTimeV3(开始时间,结束时间,dateadd(d,-30,@i),dateadd(d,-1,@i))) as c1,
convert(varchar(10),dateadd(d,-30,@i),120) as d1,convert(varchar(10),dateadd(d,-1,@i),120) as d2 from @T
union all
select '30-60天使用时间:',sum(dbo.generateTimeV3(开始时间,结束时间,dateadd(d,-60,@i),dateadd(d,-31,@i))),
convert(varchar(10),dateadd(d,-60,@i),120),convert(varchar(10),dateadd(d,-31,@i),120) from @T
union all
select '60-90天使用时间:',sum(dbo.generateTimeV3(开始时间,结束时间,dateadd(d,-90,@i),dateadd(d,-61,@i))),
convert(varchar(10),dateadd(d,-90,@i),120),convert(varchar(10),dateadd(d,-61,@i),120) from @T
) aa
/* 运行结果:
0-30天使用时间:30天 逻辑说明(2010-12-02到2010-12-31)
30-60天使用时间:30天 逻辑说明(2010-11-02到2010-12-01)
60-90天使用时间:26天 逻辑说明(2010-10-03到2010-11-01)
*/
--创建一个函数
create function generateTimeV3
(
@begin_date1 datetime,
@end_date1 datetime,
@begin_date2 datetime,
@end_date2 datetime
)
returns int
as
begin
declare @t1 table(date datetime)
insert into @t1
select dateadd(dd,number,@begin_date1) AS date
from master..spt_values
where type='p' and dateadd(dd,number,@begin_date1)<=@end_date1
declare @t2 table(date datetime)
insert into @t2
select dateadd(dd,number,@begin_date2) AS date
from master..spt_values
where type='p' and dateadd(dd,number,@begin_date2)<=@end_date2
declare @i int
select @i =count(1) from @t1 a,@t2 b where a.date=b.date
return @i
end
go
declare @T table (开户类型 varchar(5),开始时间 datetime,结束时间 datetime)
insert into @T
select '类型1','2010-01-01','2010-10-15' union all
select '类型2','2010-10-20','2010-12-15' union all
select '类型3','2010-12-12','2011-01-01'
--假设今天是 2011-01-01
declare @i datetime set @i='2011-01-01'
select dbo.generateTimeV3(开始时间,结束时间,dateadd(d,-30,@i),dateadd(d,-1,@i)) from @T
/*
0
14
20
*/
select dbo.generateTimeV3(开始时间,结束时间,dateadd(d,-60,@i),dateadd(d,-31,@i)) from @T
/*
0
30
0
*/
select dbo.generateTimeV3(开始时间,结束时间,dateadd(d,-90,@i),dateadd(d,-61,@i)) from @T
/*
13
13
0
*/
--划分的时间段
select convert(varchar(10),dateadd(d,-30,@i),120),convert(varchar(10),dateadd(d,-1,@i),120)
union all
select convert(varchar(10),dateadd(d,-60,@i),120),convert(varchar(10),dateadd(d,-31,@i),120)
union all
select convert(varchar(10),dateadd(d,-90,@i),120),convert(varchar(10),dateadd(d,-61,@i),120)
/*
2010-12-02 2010-12-31
2010-11-02 2010-12-01
2010-10-03 2010-11-01
*/
declare @T table (开户类型 varchar(5),开始时间 datetime,结束时间 datetime)
insert into @T
select '类型1','2010-01-01','2010-10-15' union all
select '类型2','2010-10-20','2010-12-15' union all
select '类型3','2010-12-12','2011-01-01'
select * from @T
--假设今天是 2011-01-01
declare @i datetime set @i='2011-01-01'