• It's much easier to visualize with data.

    Try this for example:

    DECLARE @test-2 TABLE(myDate DATE, Sales MONEY)

    INSERT INTO @test-2(myDate, Sales)

    VALUES

    ('12/30/2013',100),

    ('12/31/2013',100),

    ('01/01/2014',100),

    ('01/02/2014',100),

    ('01/03/2014',200),

    ('01/04/2014',200),

    ('01/05/2014',100),

    ('01/06/2014',100),

    ('01/07/2014',100),

    ('01/08/2014',100),

    ('01/09/2014',100),

    ('01/10/2014',250),

    ('01/11/2014',250),

    ('01/01/2015',100),

    ('01/02/2015',150),

    ('01/03/2015',150),

    ('01/04/2015',100),

    ('01/05/2015',100),

    ('01/06/2015',100),

    ('01/07/2015',100),

    ('01/08/2015',100),

    ('01/09/2015',300),

    ('01/10/2015',300)

    SELECT *, DATEPART(dw, myDate) AS Day_Week, DATEPART(isowk, myDate) AS Week_Num FROM @test-2

    SELECT DATEPART(isowk, myDate) AS Week_Num, DATEPART(yyyy, myDate) AS [Year], SUM(Sales) AS TotalSales

    FROM @test-2

    WHERE DATEPART(dw, myDate) IN (6,7) --Friday and Saturday Only

    GROUP BY DATEPART(isowk, myDate), DATEPART(yyyy, myDate)

    You can use whatever predicate you want based on your needs. I just used this example to demonstrate grouping up Fridays and Saturdays over multiple years.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned