sqlsql-serveractian

Result data that is grouped by year and a column showing previous year average price

I have simple raw data table containing monthly data for each customer and I desire to have a column in the result data that is grouped by year and a column showing previous year average price.

To obtain all records I use this SQL:

SELECT
    LEFT(D3611_Transaktionsda, 4) AS MYPERIOD,
    D3631_Antal AS MYQTY1,
    D3653_Debiterbart AS SALES,
    D3653_Debiterbart/D3631_Antal AS PRICE
FROM PUPROTRA
WHERE PUPROTRA.D3605_Artikelkod = 'XYZ'
    AND PUPROTRA.D3601_Ursprung = 'O' 
    AND PUPROTRA.D3625_Transaktionsty = 'U'
    AND D3631_Antal <> 0
ORDER BY LEFT(D3611_Transaktionsda, 4)

Thereafter I group them by MYPERIOD and intruduce SUM() for the other columns to get a SQL and result looking like below:

SELECT
    LEFT(D3611_Transaktionsda, 4) AS MYPERIOD,
    SUM(D3631_Antal) AS MYQTY1,
    SUM(D3653_Debiterbart) AS SALES,
    SUM(D3653_Debiterbart)/SUM(D3631_Antal) AS PRICE
FROM PUPROTRA
WHERE PUPROTRA.D3605_Artikelkod = 'XYZ'
    AND PUPROTRA.D3601_Ursprung = 'O'
    AND PUPROTRA.D3625_Transaktionsty = 'U'
    AND D3631_Antal <> 0
GROUP BY MYPERIOD

Image of grouped result

In order to obtain a new column containing the difference between previous year price and current year price I have been trying to use the below SQL, but apparently it doesn't work.

SELECT
    LEFT(D3611_Transaktionsda, 4) AS MYPERIOD,
    SUM(D3631_Antal) AS MYQTY1,
    SUM(D3653_Debiterbart) AS SALES,
    SUM(D3653_Debiterbart)/SUM(D3631_Antal) AS PRICE,
    LAG(SUM(D3653_Debiterbart)/SUM(D3631_Antal)) OVER 
        (PARTITION BY LEFT(D3611_Transaktionsda, 4)) AS PREV_PRICE
FROM PUPROTRA
WHERE PUPROTRA.D3605_Artikelkod = 'XYZ'
    AND PUPROTRA.D3601_Ursprung = 'O'
    AND PUPROTRA.D3625_Transaktionsty = 'U'
    AND D3631_Antal <> 0
GROUP BY MYPERIOD

I would like the result to look like below but I don't know SQL well enough to achieve this so any help is welcome.

Desired result

Solution

  • See example without CTE and WINDOW function.

    We directly calculate previous LEFT(t3.D3611_Transaktionsda,4)(YearMonth) for this row
    and calculate Prev_price as SUM(D3653_Debiterbart)/SUM(D3631_Antal) for this (YearMonth) .

    SELECT LEFT(D3611_Transaktionsda,4) AS MYPERIOD,
     SUM(D3631_Antal) AS MYQTY1,
     SUM(D3653_Debiterbart) AS SALES,
     SUM(D3653_Debiterbart)/SUM(D3631_Antal) AS PRICE,
     (select SUM(D3653_Debiterbart)/SUM(D3631_Antal) FROM PUPROTRA t2 
       where LEFT(t2.D3611_Transaktionsda,4)=
                 (select max(LEFT(t3.D3611_Transaktionsda,4)) FROM PUPROTRA t3
                  where LEFT(t3.D3611_Transaktionsda,4)<LEFT(t.D3611_Transaktionsda,4)
                    and t3.D3605_Artikelkod ='XYZ' AND t3.D3601_Ursprung='O'
                    AND t3.D3625_Transaktionsty='U' AND t3.D3631_Antal <>0)
          AND t2.D3605_Artikelkod ='XYZ' AND t2.D3601_Ursprung='O'
          AND t2.D3625_Transaktionsty='U' AND t2.D3631_Antal <>0
       ) as Prev_price
FROM PUPROTRA t WHERE t.D3605_Artikelkod ='XYZ' AND t.D3601_Ursprung='O'
     AND t.D3625_Transaktionsty='U' AND D3631_Antal <>0
GROUP BY LEFT(t.D3611_Transaktionsda,4);

    Perhaps the implementation of window functions in HCL Actian Zen 15 db Engine has limitations from the standard ones. Change LAG(SUM(..)/SUM(..)) to subquery and LAG(PRICE).

    Try this example

    SELECT MYPERIOD,MYQTY1,SALES,PRICE,
    LAG( PRICE)OVER(ORDER BY MYPERIOD) AS PREV_PRICE
FROM(
  SELECT
    LEFT(p.D3611_Transaktionsda, 4) AS MYPERIOD,
    SUM(p.D3631_Antal) AS MYQTY1,
    SUM(p.D3653_Debiterbart) AS SALES,
    SUM(p.D3653_Debiterbart) / SUM(p.D3631_Antal) AS PRICE
  FROM PUPROTRA p
  WHERE p.D3605_Artikelkod = 'XYZ'
    AND p.D3601_Ursprung='O'
    AND p.D3625_Transaktionsty = 'U'
    AND p.D3631_Antal <> 0
  GROUP BY LEFT(p.D3611_Transaktionsda, 4)
)as T
;