# Group by for multiple weeks

I have a table ParentProduct , ProductCode, BusinessDate, Price
A ParentProduct could have one or more ProductCode's

I have prices for a given ProductCode for every day for a span of 12 weeks.
So if today was 04/15/2014, the database has values for prices from 04/15/2014 to 07/08/2014

I need to sum up the values for every week, so I need the sum of the price on
4/15/2014, 4/22/2014, 4/29/2014, 5/6/2014, 5/13/2014, 5/20/2014, 5/27/2014, 6/3/2014, 6/10/2014, 6/17/2014, 6/24/2014, 7/1/2014
grouped by ParentProduct, ProductCode

If today was 04/16/2014, then the days of interest are
4/16/2014, 4/23/2014, 4/30/2014, 5/7/2014, 5/14/2014, 5/21/2014, 5/28/2014, 6/4/2014, 6/11/2014, 6/18/2014, 6/25/2014, 7/2/2014
LVL 1
###### Who is Participating?

x

Commented:
Surely it has to be more complex than this:
``````DECLARE @StartDate date = DATEADD(DAY, 1, DATEDIFF(day, 0, GETDATE()))

SELECT  ParentCode,
ProductCode,
SUM(Price)
FROM    YourTable
AND DATEPART(WEEKDAY, BusinessDate) = DATEPART(WEEKDAY, @StartDate)
GROUP BY ParentCode,
ProductCode,
``````
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Shows us some mockup data and what you'd like to see returned for what you're trying to pull off here.
0

Author Commented:
Here is some data, please note that threre is data for every day twelve weeks out

EVEN                  MARB            2014-04-15            93615.738950
EVEN                  MARB            2014-04-16            94424.297350
EVEN                  MARB            2014-04-17            95246.313350
EVEN                  MARB            2014-04-18            95977.294675
EVEN                  MARB            2014-04-21            95957.044400
EVEN                  MARB            2014-04-22            95846.721800
EVEN                  MARB            2014-04-23            95735.846200
EVEN                  MARB            2014-04-24            95624.407000
EVEN                  MARB            2014-04-25            95512.393200
EVEN                  SSIT            2014-04-15            50259.680700
EVEN                  SSIT            2014-04-16            48824.828100
EVEN                  SSIT            2014-04-17            47445.000150
EVEN                  SSIT            2014-04-18            46418.065500
0

Senior DBACommented:
SELECT
ParentProduct, ProductCode, SUM(Price) AS Price
-- generate a separate row for every week, 12 total
FROM (
SELECT 0 AS week_increment UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11
) AS week_increments
CROSS APPLY (
) AS week_dates
-- join to the main table by matching business date
INNER JOIN dbo.tablename t ON
GROUP BY ParentProduct, ProductCode
ORDER BY ParentProduct, ProductCode

You can run the first part by itself to see the week date ranges that are being generated:
SELECT *
FROM (
SELECT 0 AS week_increment UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11
) AS week_increments
CROSS APPLY (
) AS week_dates
0

Author Commented:
Scott,

Can you please let me know where cj2 has beend defined in the above solution
0

Senior DBACommented:
Sorry, that was a typo.  I edited to correct those.

Please re-copy the query and try again.
0

Author Commented:
0

Senior DBACommented:
Sorry, I misunderstood the requirements.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.