--测试数据
create table #tb(pk int identity(1,1),years datetime)insert #tb
select top 200 cast('2008-09-10' as datetime)+(select count(1) from sys.objects where name<a.name)
from sys.objects a
order by name
--去掉星期六与星期日(与本机datefirst有关)
delete from #tb where datepart(dw,years) in (1,7)问题:如何提取每周最后存在数据的一天?
说明
1、若某周星期五没数据,则取星期四,依次推。某周没数据,则无;
注:测试数据周一到周五都有数据,没做处理。
2、周存在跨年问题
create table #tb(pk int identity(1,1),years datetime)insert #tb
select top 200 cast('2008-09-10' as datetime)+(select count(1) from sys.objects where name<a.name)
from sys.objects a
order by name
--去掉星期六与星期日(与本机datefirst有关)
delete from #tb where datepart(dw,years) in (1,7)问题:如何提取每周最后存在数据的一天?
说明
1、若某周星期五没数据,则取星期四,依次推。某周没数据,则无;
注:测试数据周一到周五都有数据,没做处理。
2、周存在跨年问题
--按照這個周是一年中的第幾周分組取最大weekday
SELECT max(datepart(weekday,crdate)),datepart(week,crdate) FROM sysobjects group by datepart(week,crdate)
from #tb
group by datepart(wk,years)
select datepart(week,years) week,max(years) maxyears
from #tb
where datepart(week,years)<>datepart(week,cast(cast(year('2008-12-31') as varchar(4))+'/12/31' as datetime))
OR not exists(select 1 from #tb where years<=dateadd(day,7-datepart(weekday,years),years))
group by datepart(week,years)
order by maxyears
from ta
where rowid = 1
from #tb a
where datepart(week,years)<>datepart(week,cast(cast(year(years) as varchar(4))+'/12/31' as datetime))
OR (
datepart(week,years)=datepart(week,cast(cast(year(years) as varchar(4))+'/12/31' as datetime))
and not exists(select 1 from #tb b where b.years between a.years+1 and dateadd(day,7-datepart(weekday,a.years),a.years))
)
group by datepart(week,years)
order by maxyears