Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group by for multiple weeks

Posted on 2014-04-15
8
Medium Priority
?
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

610 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