CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

How to find sum of Next quarter values

Please find table structure and data below

CREATE TABLE Table1
    ([FORECAST_YEAR] int, [FORECAST_MONTH] int, [QUARTER] int, [ITEM_MASTER_SID] int, [COMPANY_MASTER_SID] int, [UNITS] int)
;

INSERT INTO Table1
    ([FORECAST_YEAR], [FORECAST_MONTH], [QUARTER], [ITEM_MASTER_SID], [COMPANY_MASTER_SID], [UNITS])
VALUES
    (2016, 1, 1, 18, 172496, 10000),
    (2016, 2, 1, 18, 172496, 11000),
    (2016, 3, 1, 18, 172496, 12000),
    (2016, 4, 2, 18, 172496, 13000),
    (2016, 5, 2, 18, 172496, 14000),
    (2016, 6, 2, 18, 172496, 15000),
    (2016, 7, 3, 18, 172496, 16000),
    (2016, 8, 3, 18, 172496, 17000),
    (2016, 9, 3, 18, 172496, 18000),
    (2016, 10, 4, 18, 172496, 19000),
    (2016, 11, 4, 18, 172496, 20000),
    (2016, 12, 4, 18, 172496, 21000)
;

Now for each company_master_sid and item_master_sid i have to calculate present quarter units and next quarter units. For that i have used below query

SELECT a.FORECAST_YEAR,
       a.FORECAST_MONTH,
       a.QUARTER,
       a.COMPANY_MASTER_SID,
       a.ITEM_MASTER_SID,
       A.UNITS,
       a.PRESENT_QUARTER_VALUE,
       A.Next_month_value,
       Sum(Next_month_value)
         OVER(
           partition BY company_master_sid, item_master_sid, quarter,
                                         [FORECAST_YEAR]) AS next_Quarter_value
FROM   (SELECT *,
               Sum(units)
                 OVER(
                   partition BY company_master_sid, item_master_sid,
                                  quarter, [FORECAST_YEAR]) AS PRESENT_QUARTER_VALUE,
               Lead(units)
                 OVER(
                   partition BY company_master_sid, item_master_sid, 
                                        forecast_month%3
                   ORDER BY forecast_month) AS Next_month_value
        FROM   #Table1)a
ORDER  BY FORECAST_MONTH

But please suggest how to simply above query in a single select, because i have more than two variables like units in table1 and also i have to find Present and next quarter values for those variables.

My Required Output is looks like below

FORECAST_YEAR   FORECAST_MONTH  QUARTER COMPANY_MASTER_SID  ITEM_MASTER_SID UNITS   PRESENT_QUARTER_VALUE   NEXT_MONTH_VALUE    NEXT_QUARTER_VALUE
        2016        1               1       172496                  18      10000       33000                       13000       42000
        2016        2               1       172496                  18      11000       33000                       14000       42000
        2016        3               1       172496                  18      12000       33000                       15000       42000
        2016        4               2       172496                  18      13000       42000                       16000       51000
        2016        5               2       172496                  18      14000       42000                       17000       51000
        2016        6               2       172496                  18      15000       42000                       18000       51000
        2016        7               3       172496                  18      16000       51000                       19000       60000
        2016        8               3       172496                  18      17000       51000                       20000       60000
        2016        9               3       172496                  18      18000       51000                       21000       60000
        2016        10              4       172496                  18      19000       60000                       NULL        NULL
        2016        11              4       172496                  18      20000       60000                       NULL        NULL
        2016        12              4       172496                  18      21000       60000                       NULL        NULL

Above select query returning same result set as posted please suggest to simplify the query as a single select.

Upvotes: 3

Views: 189

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

This works:Demo here

;with cte
 as
 (
select 
*,
sum(units) over (partition by quarter) as quartersum
from 
#table1
)
select cte.*,b.*
from cte
outer apply
(
select units,quartersum from #table1 t2
where cte.quarter+1=t2.quarter
)b

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Try this one

SELECT T1.[FORECAST_YEAR], T1.[FORECAST_MONTH], T1.[QUARTER], T1.[ITEM_MASTER_SID], T1.[COMPANY_MASTER_SID], T1.[UNITS]
        ,SUM(CASE WHEN T2.[QUARTER] = T1.[QUARTER] THEN T2.[UNITS] ELSE 0 END ) PRESENT_QUARTER_VALUE
        ,SUM(CASE WHEN T2.[FORECAST_MONTH] = T1.[FORECAST_MONTH]+1 THEN T2.[UNITS] ELSE 0 END ) NEXT_MONTH_VALUE
        ,SUM(CASE WHEN T2.[QUARTER] = T1.[QUARTER]+1 THEN T2.[UNITS] ELSE 0 END ) NEXT_QUARTER_VALUE
FROM Table1 T1
INNER JOIN Table1 T2 ON T2.[ITEM_MASTER_SID] = T1.[ITEM_MASTER_SID]  AND T2.[COMPANY_MASTER_SID] = T1.[COMPANY_MASTER_SID] 
GROUP BY T1.[FORECAST_YEAR], T1.[FORECAST_MONTH], T1.[QUARTER], T1.[ITEM_MASTER_SID], T1.[COMPANY_MASTER_SID], T1.[UNITS]

OR you can try this

SELECT [FORECAST_YEAR], [FORECAST_MONTH], [QUARTER], [ITEM_MASTER_SID], [COMPANY_MASTER_SID], [UNITS]
        ,(  SELECT SUM([UNITS]) FROM    Table1  T2 
            WHERE T2.[ITEM_MASTER_SID] = T1.[ITEM_MASTER_SID] 
                AND T2.[COMPANY_MASTER_SID] = T1.[COMPANY_MASTER_SID] 
                AND T2.[QUARTER] = T1.[QUARTER])    PRESENT_QUARTER_VALUE
        ,(  SELECT SUM([UNITS]) FROM    Table1  T2 
            WHERE T2.[ITEM_MASTER_SID] = T1.[ITEM_MASTER_SID] 
                AND T2.[COMPANY_MASTER_SID] = T1.[COMPANY_MASTER_SID] 
                AND T2.[FORECAST_MONTH] = T1.[FORECAST_MONTH]+1)    NEXT_MONTH_VALUE
        ,(  SELECT SUM([UNITS]) FROM    Table1  T2 
            WHERE T2.[ITEM_MASTER_SID] = T1.[ITEM_MASTER_SID] 
                AND T2.[COMPANY_MASTER_SID] = T1.[COMPANY_MASTER_SID] 
                AND T2.[QUARTER] = T1.[QUARTER]+1)  NEXT_QUARTER_VALUE
FROM Table1 T1

Upvotes: 0

Related Questions