Solved

Group by for multiple weeks

Posted on 2014-04-15
8
210 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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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:ScottPletcher
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:ScottPletcher
ID: 40002741
Sorry, I misunderstood the requirements.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now