当NO=1时,代表当天
ID Name No TransDate
1 任务1 1 2010-10-13如何根据一条纪录生成一周的纪录-100分
当NO=2时,代表本周
ID Name No TransDate
2 任务2 2 2010-10-13如何根据一条纪录生成当前月的纪录-100分 http://topic.csdn.net/u/20101013/10/c82d2d43-2182-41e9-9e05-d4ad36cdd0e4.html
当NO=3时,代表本月
ID Name No TransDate
3 任务3 3 2010-10-13
ID Name No TransDate
1 任务1 1 2010-10-13如何根据一条纪录生成一周的纪录-100分
当NO=2时,代表本周
ID Name No TransDate
2 任务2 2 2010-10-13如何根据一条纪录生成当前月的纪录-100分 http://topic.csdn.net/u/20101013/10/c82d2d43-2182-41e9-9e05-d4ad36cdd0e4.html
当NO=3时,代表本月
ID Name No TransDate
3 任务3 3 2010-10-13
declare @date datetime
set @date='2009-02-05'
select [day]=convert(varchar,DATEADD(mm,DATEDIFF(mm,0,@date),0)+number,112)
from master..spt_values
where type='p'
and number< datediff(dd,@date,dateadd(mm,1,@date))
ID Name No TransDate
2 任务2 2 2010-10-13判断TransDate的日期,生成(一星期)纪录...
ID Name No TransDate
2 任务2 1 2010-10-11
2 任务2 1 2010-10-12
2 任务2 1 2010-10-13
2 任务2 1 2010-10-14
2 任务2 1 2010-10-15
2 任务2 1 2010-10-16
2 任务2 1 2010-10-17
稍微花点时间看看都有了
datediff(m,(select top 1 TransDate from 任务表 b where b.ID = @参数 ),a.TransDate) = 0
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, Name varchar(8), No int, TransDate datetime)
insert into #
select 1, '任务1', 1, '2010-10-13' union all
select 2, '任务2', 2, '2010-10-13' union all
select 3, '任务3', 3, '2010-10-13'declare @min datetime, @max datetime
select @min=min(TransDate)-31, @max=max(TransDate)+31 from #;with cte as
(
select *,
stardate = dateadd(day, (case No
when 1 then 0
when 2 then -(@@datefirst+datepart(weekday,TransDate)-1)%7
when 3 then 1-day(TransDate) end), TransDate),
enddate = dateadd(day, (case No
when 1 then 0
when 2 then 6-(@@datefirst+datepart(weekday,TransDate)-1)%7
when 3 then 32-day(TransDate+32-day(TransDate))-day(TransDate) end), TransDate)
from #
)
select a.ID, a.Name, a.No, TransDate=dateadd(day,b.number,@min) from cte a, master..spt_values b where b.type='P'
and b.number <= datediff(day,@min,@max)
and dateadd(day,b.number,@min) between a.stardate and a.enddate/*
ID Name No TransDate
----------- -------- ----------- -----------------------
1 任务1 1 2010-10-13 00:00:00.000
2 任务2 2 2010-10-10 00:00:00.000
2 任务2 2 2010-10-11 00:00:00.000
2 任务2 2 2010-10-12 00:00:00.000
2 任务2 2 2010-10-13 00:00:00.000
2 任务2 2 2010-10-14 00:00:00.000
2 任务2 2 2010-10-15 00:00:00.000
2 任务2 2 2010-10-16 00:00:00.000
3 任务3 3 2010-10-01 00:00:00.000
3 任务3 3 2010-10-02 00:00:00.000
3 任务3 3 2010-10-03 00:00:00.000
3 任务3 3 2010-10-04 00:00:00.000
3 任务3 3 2010-10-05 00:00:00.000
3 任务3 3 2010-10-06 00:00:00.000
3 任务3 3 2010-10-07 00:00:00.000
3 任务3 3 2010-10-08 00:00:00.000
3 任务3 3 2010-10-09 00:00:00.000
3 任务3 3 2010-10-10 00:00:00.000
3 任务3 3 2010-10-11 00:00:00.000
3 任务3 3 2010-10-12 00:00:00.000
3 任务3 3 2010-10-13 00:00:00.000
3 任务3 3 2010-10-14 00:00:00.000
3 任务3 3 2010-10-15 00:00:00.000
3 任务3 3 2010-10-16 00:00:00.000
3 任务3 3 2010-10-17 00:00:00.000
3 任务3 3 2010-10-18 00:00:00.000
3 任务3 3 2010-10-19 00:00:00.000
3 任务3 3 2010-10-20 00:00:00.000
3 任务3 3 2010-10-21 00:00:00.000
3 任务3 3 2010-10-22 00:00:00.000
3 任务3 3 2010-10-23 00:00:00.000
3 任务3 3 2010-10-24 00:00:00.000
3 任务3 3 2010-10-25 00:00:00.000
3 任务3 3 2010-10-26 00:00:00.000
3 任务3 3 2010-10-27 00:00:00.000
3 任务3 3 2010-10-28 00:00:00.000
3 任务3 3 2010-10-29 00:00:00.000
3 任务3 3 2010-10-30 00:00:00.000
3 任务3 3 2010-10-31 00:00:00.000
*/
use tempdb;
go
if object_id('dbo.tb') is not null drop table dbo.tb
create table dbo.tb(ID int, Name varchar(8), bh int, test_date datetime)
insert into dbo.tb
select 1, '任务1', 1, '2010-10-13' union all
select 2, '任务2', 2, '2010-10-13' union all
select 3, '任务3', 3, '2010-10-13';
gocreate function dbo.fn_number
(
@bh int,
@test_date datetime
)
returns @table table
(dt datetime)
as
begin
if @bh=1
begin
insert into @table
select @test_date;
end
if @bh=2
begin
insert into @table
select dateadd(day,number-(datepart(weekday,@test_date+@@datefirst-1)),@test_date)
from master..spt_values
where type='p'
and number between 1 and 7;
end
if @bh=3
begin
insert into @table
select dateadd(day,number,cast(convert(char(8),@test_date,120)+'01'as datetime))
from master..spt_values
where type='p'
and number between 0 and DATEdiff(DAY,@test_date,dateadd(month,1,@test_date));
end
return
end
goselect *
from dbo.tb as t
cross apply dbo.fn_number(t.bh,t.test_date);/**
省略 但 保证完全正确
**/