Grouping by the Week by using datepart function : DATEPART « Date Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Date Functions
3. DATEPART

4>
5>
6> CREATE TABLE Orders (
7>      OrderID int IDENTITY (1, 1) NOT NULL ,
8>      CustomerID nchar (5) NULL ,
9>      EmployeeID int NULL ,
10>     OrderDate datetime NULL ,
11>     RequiredDate datetime NULL ,
12>     ShippedDate datetime NULL ,
13>     ShipVia int NULL ,
14>     Freight money NULL DEFAULT (0),
15>     ShipName nvarchar (40) NULL ,
16>     ShipAddress nvarchar (60) NULL ,
17>     ShipCity nvarchar (15) NULL ,
18>     ShipRegion nvarchar (15) NULL ,
19>     ShipPostalCode nvarchar (10) NULL ,
20>     ShipCountry nvarchar (15) NULL
21> )
22> GO
1>
2>
3> SELECT od - wd + 1 AS week_start, od + 7 - wd AS week_end,
4>   COUNT(*) AS numorders
5> FROM (SELECT OrderID AS oid, OrderDate AS od,
6>         DATEPART(weekday, OrderDate + @@DATEFIRST - 1) AS wd
7>       FROM dbo.Orders) AS D
8> GROUP BY od - wd + 1, od + 7 - wd;
9> GO
week_start              week_end                numorders
----------------------- ----------------------- -----------

(0 rows affected)
1>
2> drop table Orders;
3> GO
1>
2>