Mega Code Archive
Grouping by the Week by using datepart function
<![CDATA[
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>
]]>