Hello,

Please find an example sql below. Hope it helps.

-- Drop table if it already exists

IF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)

BEGIN

DROP TABLE #tmpOrders ;

END ;

-- Create temp table to hold orders

CREATE TABLE #tmpOrders

(

OrderNo INT IDENTITY (1, 1) NOT NULL

, OrderQuantity INT NOT NULL

, OrderDate DATE NOT NULL

) ;

-- Insert orders

INSERT INTO #tmpOrders

SELECT o.OrderQuantity

, o.OrderDate

FROM (

VALUES ('100', '2014-03-01')

, ('200', '2014-03-10')

, ('200', '2014-03-09')

, ('300', '2014-03-08')

, ('300', '2014-03-02')

, ('300', '2014-03-07')

, ('400', '2013-03-10')

, ('500', '2013-03-10')

, ('700', '2013-02-10')

, ('800', '2013-01-10')

) o(OrderQuantity, OrderDate)

ORDER BY o.OrderDate ;

-- Select orders

-- SELECT * FROM #tmpOrders ;

-- Set 'Sunday' as the first day of the week (SELECT @@DATEFIRST ;)

SET DATEFIRST 7 ;

-- Select the Windowed data

SELECT *

FROM #tmpOrders o

CROSS APPLY (

SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))

) xWindow(WindowingValue) ;

-- Select the order counts

SELECT Window = xwindow.WindowingValue

, OrderCount = COUNT(*)

FROM #tmpOrders o

CROSS APPLY (

SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))

) xWindow(WindowingValue)

GROUP BY xwindow.WindowingValue ;

-- Cleanup

IF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)

BEGIN

DROP TABLE #tmpOrders ;

END ;

GO