SQLTeam.com | Weblogs | Forums

Count by Week, T-SQL


#1

I'm looking to modify a query, changing it from a daily count to a count by week (Sun-Sat). Is there a function to do that easily?

Here's my current query:

SELECT Convert(date, [INSTANT_OF_ERROR_DTTM]) as ErrorDate,
count([INSTANT_OF_ERROR_DTTM]) as ErrorCount
FROM [clarity].[dbo].[INTERFACE_ERROR_LOG]
where [INSTANT_OF_ERROR_DTTM] >= '2017-02-01'
Group By Convert(date, [INSTANT_OF_ERROR_DTTM])
order by Convert(date, [INSTANT_OF_ERROR_DTTM])

Thanks in advance!


#2

datepart(week,[instant_of_error_dttm])


#3
SELECT Cast(DATEADD(DAY, DATEDIFF(DAY, [INSTANT_OF_ERROR_DTTM], 6) % 7,
    [INSTANT_OF_ERROR_DTTM]) AS date) as ErrorWeekStart,
count([INSTANT_OF_ERROR_DTTM]) as ErrorCount
FROM [clarity].[dbo].[INTERFACE_ERROR_LOG]
where [INSTANT_OF_ERROR_DTTM] >= '2017-02-01'
Group By Cast(DATEADD(DAY, DATEDIFF(DAY, [INSTANT_OF_ERROR_DTTM], 6) % 7, [INSTANT_OF_ERROR_DTTM]) AS date)
order by Cast(DATEADD(DAY, DATEDIFF(DAY, [INSTANT_OF_ERROR_DTTM], 6) % 7, [INSTANT_OF_ERROR_DTTM]) AS date)

#4

Thanks for the suggestions! In case it helps anyone in the future, here's what I ended up with:

SELECT datepart(year,[instant_of_error_dttm]) as ErrorYear,
datepart(week,[instant_of_error_dttm]) as ErrorWeek,
concat(datepart(year,[instant_of_error_dttm]), ' Wk ', datepart(week,[instant_of_error_dttm])) as YrWk,
count(*) as ErrorCount
 FROM [db].[dbo].[INTERFACE_ERROR_LOG]
where [INSTANT_OF_ERROR_DTTM] >= '2017-01-01'
  and AIP_RECORD_ID > 500000
Group By datepart(year,[instant_of_error_dttm]), datepart(week,[instant_of_error_dttm])
order by datepart(year,[instant_of_error_dttm]), datepart(week,[instant_of_error_dttm])

#5

Again, be aware that instance settings can change the results returned by "datepart(week,)", viz:

set language german
select datepart(week,'20170311')
set language english
select datepart(week,'20170311')