?
Solved

Group by for multiple weeks

Posted on 2014-04-15
8
Medium Priority
?
223 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 66

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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

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 70

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 70

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 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