CODEWITHSUNDEEP
sqlsql-serversum
mnms2
mnms2

Reputation: 19

Aggregation per Date

I have thousands of companies listed but for illustration; I cited 2 companies. I need to produce the column TotalSales in which values are the sum of sales per company , a year prior to its corresponding actual year & quarter.

Company         Sales   Quarter    Year      TotalSales    QtrYr_Included
ABC Inc.        10,000     1       2010         null       Q12009 - Q42009
ABC Inc.        50,000     2       2010        10,000      Q22009 - Q12010
ABC Inc.        35,000     3       2010        60,000      Q32009 - Q22010 
ABC Inc.        15,000     4       2010        95,000      Q42009 - Q32010
ABC Inc.         5,000     1       2011        110,000     Q12010 - Q42010
ABC Inc.        10,000     2       2011        105,000     Q22010 - Q12011
SoKor Group     50,000     1       2009         null       Q12008 - Q42008
SoKor Group     10,000     2       2009        50,000      Q22008 - Q12009
SoKor Group     10,000     3       2009        60,000      Q32008 - Q22009
SoKor Group      5,000     4       2009        70,000      Q42008 - Q32009
SoKor Group     15,000     1       2010          .         Q12009 - Q42009
SoKor Group     20,000     3       2010          .         Q22009 - Q12010

Thank you so much.

Upvotes: 2

Views: 94

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

@Prdp's solution is valid. However, it would show incorrect results when there are quarters missing for a given company as it will consider whatever row was available before the missing row. A way to avoid such situation is using derived tables to generate all combinations of year,quarter and company. Left joining the original table on to this result would generate 0 sales for the missing quarters. Then use the sum window function to get the sum of sales for the last 4 quarters for each row.

SELECT *
FROM
 (SELECT C.COMPANY,
         Y.[YEAR],
         Q.[QUARTER],
         T.SALES,
         SUM(COALESCE(T.SALES,0)) OVER(PARTITION BY C.COMPANY
                                       ORDER BY Y.[YEAR], Q.[QUARTER] 
                                       ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS PREV_4QTRS_TOTAL
  FROM
   (SELECT 2008 AS [YEAR]
    UNION ALL SELECT 2009
    UNION ALL SELECT 2010
    UNION ALL SELECT 2011
    UNION ALL SELECT 2012
    UNION ALL SELECT 2013) Y --Add more years as required or generate them using a recursive cte or a tally table
  CROSS JOIN
   (SELECT 1 AS [QUARTER]
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4) Q
  CROSS JOIN
   (SELECT DISTINCT COMPANY
    FROM T) C
  LEFT JOIN T ON Y.[YEAR]=T.[YEAR]
  AND Q.[QUARTER]=T.[QUARTER]
  AND C.COMPANY=T.COMPANY 
) X
WHERE SALES IS NOT NULL --to filter the result to include only rows from the original table
ORDER BY 1,2,3

Sample Demo

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one way to do it using Sum Over window aggregate

SELECT *,
       Sum(sales)
         OVER(
           partition BY Company
           ORDER BY [Year], [Quarter] ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)
FROM   Yourtable

for Older versions 

;WITH cte
     AS (SELECT Row_number()OVER(partition BY Company ORDER BY [Year], [Quarter]) rn,*
         FROM   Yourtable a)
SELECT *
FROM   cte a
       CROSS apply (SELECT Sum (sales) Total_sales
                    FROM   (SELECT TOP 4 sales
                            FROM   cte b
                            WHERE  a.Company = b.Company
                                   AND b.rn < a.rn
                            ORDER  BY [Year] DESC,
                                      [Quarter] DESC)a) cs

Upvotes: 3

Related Questions