各位高人，看一下这SQL还能不能再优化一下
用于计算当年周次
select number
,right(convert(varchar,case when year(dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))) < year(getdate())
then cast(year(getdate()) as varchar)+'-1-1'
else dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))
end,102),5) + '~' +
right(convert(varchar,case when year(dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))+6) > year(getdate())
then cast(year(getdate()) as varchar)+'-12-31'
else dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))+6
end,102),5) as date_between
from master..spt_values
where type='p'
and number < datepart(ww,cast(year(getdate()) as varchar)+'-12-31')+1
and number > 0
WITH tt
AS ( SELECT DATEPART(wk,
DATEADD(dd, m.number,
DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()),
0))) AS wk ,
DATEADD(dd, m.number,
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AS dd
FROM master..spt_values m
WHERE type = 'P'
),
t1
AS ( SELECT wk ,
CONVERT(VARCHAR, DATEPART(mm, dd)) + '.'
+ CONVERT(VARCHAR, DATEPART(dd, dd)) AS cdd
FROM TT
WHERE DATEDIFF(yy, dd, GETDATE()) = 0
)
SELECT wk ,
MIN(cdd) + '~' + MAX(cdd) AS datebetween
FROM T1
GROUP BY wk
--格式化了一下，疯了
select number,
right(convert(varchar,
case
when year(dateadd(dd,
(datepart(dw,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as
varchar) + '-1-1') - 1) * -1,
cast(year(getdate()) as varchar) +
'-1-1')) - 1) * -1,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as varchar) +
'-1-1') - number) * -1,
cast(year(getdate()) as varchar) + '-1-1'))) <
year(getdate()) then
cast(year(getdate()) as varchar) + '-1-1'
else
dateadd(dd,
(datepart(dw,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as varchar) +
'-1-1') - 1) * -1,
cast(year(getdate()) as varchar) + '-1-1')) - 1) * -1,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as varchar) + '-1-1') -
number) * -1,
cast(year(getdate()) as varchar) + '-1-1'))
end,
102),
5) + '~' + right(convert(varchar,
case
when year(dateadd(dd,
(datepart(dw,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as
varchar) + '-1-1') - 1) * -1,
cast(year(getdate()) as varchar) +
'-1-1')) - 1) * -1,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as varchar) +
'-1-1') - number) * -1,
cast(year(getdate()) as varchar) + '-1-1')) + 6) >
year(getdate()) then
cast(year(getdate()) as varchar) + '-12-31'
else
dateadd(dd,
(datepart(dw,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as varchar) +
'-1-1') - 1) * -1,
cast(year(getdate()) as varchar) + '-1-1')) - 1) * -1,
dateadd(ww,
(datepart(ww,
cast(year(getdate()) as varchar) + '-1-1') -
number) * -1,
cast(year(getdate()) as varchar) + '-1-1')) + 6
end,
102),
5) as date_between
from master .. spt_values
where type = 'p'
and number <
datepart(ww, cast(year(getdate()) as varchar) + '-12-31') + 1
and number > 0
;with t
as
(
select number,
dateadd(day,number,datename(year,getdate()) +'-01-01') as day,
datepart(ww,dateadd(day,number,datename(year,getdate()) +'-01-01')) as week_day
from master..spt_values
where type='p'
and number <= datediff(day,
datename(year,getdate()) +'-01-01',
datename(year,getdate()) +'-12-31'
)
and number >= 0
),
tt
as
(
select number,
day,
week_day,
substring(convert(varchar(10),(min(day) over(partition by week_day)),120),6,5) as week_first_day,
substring(convert(varchar(10),(max(day) over(partition by week_day)),120),6,5) as week_last_day
from t
)
select distinct week_day as number,
replace(week_first_day+'~'+week_last_day,'-','.') as date_between
from tt
