Count number of rows each week with a given starting date in SQL Server

Question

I want to select counts of each order per week. Starting from a given date. Given this set of data below from SQL Server. How can I query it that will show the list of weeks and the corresponding count of rows/orders.

This is my desired output

Answer 1

You can group by DATEPART(wk). Please see DATEPART (Transact-SQL).

It could be something like:

select DATEPART(yyyy,created_by_dt) as Year, DATEPART(wk, created_by_dt) as Week, count(*) as Count from X 
group by DATEPART(yyyy,created_by_dt), DATEPART(wk, created_by_dt)
order by DATEPART(yyyy,created_by_dt), DATEPART(wk, created_by_dt)

The following script will give you a nicer output:

select 
-- Start of week from https://stackoverflow.com/questions/1267126/get-the-week-start-date-and-week-end-date-from-week-number-in-sql-server
-- by Tomalak
CONVERT(date, DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, created_by_dt)) % 7, created_by_dt)) AS Monday, 
count(*) as Count, 
MIN(created_by_dt) AS FirstRecordThisWeek, 
CONVERT(date,MIN(created_by_dt)) AS FirstRecordThisWeek_date
from X 
group by  CONVERT(date, DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, created_by_dt)) % 7, created_by_dt))
order by CONVERT(date, DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, created_by_dt)) % 7, created_by_dt))

Output (from my data):

Monday      Count   FirstRecordThisWeek         FirstRecordThisWeek_date
2018-12-31  1       2019-01-03 05:11:30.570     2019-01-03
2019-03-11  1       2019-03-13 06:03:41.500     2019-03-13
2019-04-01  2       2019-04-04 01:16:34.440     2019-04-04
2019-04-15  21      2019-04-15 04:25:30.913     2019-04-15
2019-04-29  6       2019-04-30 06:52:26.057     2019-04-30
2019-05-06  3       2019-05-08 07:01:00.683     2019-05-08
2019-05-13  5       2019-05-16 22:58:43.610     2019-05-16