I am trying to SUM prior year data from a subquery (MATRIX). Basically…here’s what I tried but it produces the same data as the current data (incorrect).
SELECT
SUM(MATRIX.TOTALH)
OVER
(
PARTITION BY
YEAR(MATRIX.DATE) - 1,
MONTH(MATRIX.DATE),
DAY(MATRIX.DATE)
)
AS TOTALH_PY
FROM
(
) MATRIX
What is the issue with this?
I tried using SUM() OVER(PARTITION BY) with the year being YEAR(<date>) – 1 but this did not work. I was expecting it to produce the SUM for the same day over the prior year.
3
Can you try:
SELECT
SUM(M.TOTALH) as TOTALH_PY
FROM MATRIX M
WHERE M.DATE
BETWEEN add_years(M.DATE date('001-01-01'), YEAR(SELECT current date FROM sysibm.sysdummy1) - 1)
And
add_days((SELECT current date FROM sysibm.sysdummy1), -DAYOFYEAR(SELECT current date FROM sysibm.sysdummy1))
Maybe try this (doesn’t account for leap years):
SELECT SUM(MATRIX.TOTALH)
OVER (PARTITION BY MATRIX.DATE
ORDER BY DAYS(MATRIX.DATE) --convert date to a number, to allow to use "RANGE" clause
RANGE BETWEEN 365 PRECEDING AND 365 PRECEDING --looking just at that one day "exactly" one year ago
)
FROM MATRIX
Naturally, rows from the year before (which You want to SUM) must be a part of all rows selected. Otherwise this OLAP function (SUM OVER) would not “find” them in the resultset.