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])
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)
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])