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
countrymeisterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
Surely it has to be more complex than this:
DECLARE @StartDate date = DATEADD(DAY, 1, DATEDIFF(day, 0, GETDATE()))

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

Open in new window

0
 
Jim HornMicrosoft 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
 
countrymeisterAuthor Commented:
Here is some data, please note that threre is data for every day twelve weeks out

ParentProduct      ProductCode      BusinessDate      Price
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherSenior 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 (
    SELECT DATEADD(WEEK, week_increment, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS week_start,
        DATEADD(WEEK, week_increment + 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS week_end
) AS week_dates
-- join to the main table by matching business date
INNER JOIN dbo.tablename t ON
    t.BusinessDate >= week_dates.week_start AND
    t.BusinessDate < week_dates.week_end
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 (
    SELECT DATEADD(WEEK, week_increment, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS week_start,
        DATEADD(WEEK, week_increment + 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS week_end
) AS week_dates
0
 
countrymeisterAuthor Commented:
Scott,

Can you please let me know where cj2 has beend defined in the above solution
0
 
Scott PletcherSenior DBACommented:
Sorry, that was a typo.  I edited to correct those.

Please re-copy the query and try again.
0
 
countrymeisterAuthor Commented:
Had to go with your solution, and then pivot the dates
0
 
Scott PletcherSenior 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.

All Courses

From novice to tech pro — start learning today.