Sqlserver 获取每周的数据

--指定周的星期一
SELECT    DATEADD(wk,  DATEDIFF(wk,0,dateadd(wk , 第几周 , 年份+'-01-01')),  0) - 7
--指定周的星期日

SELECT    DATEADD(wk,  DATEDIFF(wk,0,dateadd(wk , 第几周 , 年份+'-01-01')),  0) - 1

--获取当前第几周
SELECT DATEPART(week,getdate())

需求:获取近12周的汇总数据

SELECT Count(a.BusId) BusCount,
Convert(decimal(18,2),Sum(CPrice)/10000) SumPrice,
ABS(DATEPART(week,CreateDate)-(DATEPART(week,GETDATE())+1)) as Week
FROM dbo.Crm_Business a 
where YEAR(CreateDate)=YEAR(GETDATE()) and DATEPART(week,CreateDate)>(DATEPART(week,GETDATE())-12)
group by DATEPART(week,CreateDate) 
order by DATEPART(week,CreateDate) desc

展示:

 

posted @ 2021-12-08 18:31  渴死的鱼丶  阅读(809)  评论(0编辑  收藏  举报