大概有两种方式实现,
本周 DATEPART(wk, rec_time) = DATEPART(wk, GETDATE())
上一周 DATEPART(wk, rec_time) = DATEPART(wk, GETDATE()) - 1
第一种
SET datefirst 1 SELECT
*
FROM
PEOPLECOUNT2
WHERE
(
DATEPART(wk, rec_time) = DATEPART(wk, GETDATE()) - 1
)
第二种
declare @dateValue datetime, @startDay datetime
set @dateValue = CONVERT(varchar(100), GETDATE(), 23)
set @startDay = dateadd(d, CASE WHEN datepart(dw, @dateValue) + @@DATEFIRST > 8 THEN 8 - @@datefirst ELSE 1 - @@datefirst END, dateadd(d, - datepart(dw, @dateValue) + 1, @dateValue))
select rec_time,max(people_in) peopleIn,datename(weekday,rec_time) times
from PEOPLECOUNT2
where rec_time>= @startDay and rec_time <dateadd(d, 7, @startDay)
group by datename(weekday,rec_time),rec_time