Solved

Group by for multiple weeks

Posted on 2014-04-15
8
212 Views
Last Modified: 2014-04-15
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
0
Comment
Question by:countrymeister
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40002283
Shows us some mockup data and what you'd like to see returned for what you're trying to pull off here.
0
 
LVL 1

Author Comment

by:countrymeister
ID: 40002391
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40002417
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002447
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
 
LVL 1

Author Comment

by:countrymeister
ID: 40002482
Scott,

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

Expert Comment

by:Scott Pletcher
ID: 40002486
Sorry, that was a typo.  I edited to correct those.

Please re-copy the query and try again.
0
 
LVL 1

Author Closing Comment

by:countrymeister
ID: 40002643
Had to go with your solution, and then pivot the dates
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002741
Sorry, I misunderstood the requirements.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question