I spent hours, but I just cant see it. Does anyone see what's wrong with my manual query (OAS DV)?
WITH RAC_SUM AS (
SELECT
SUBSTR(RAC, 7, 11) AS R_CIB,
DATE,
SUM(END_DAY) AS RAC_STANJE
FROM TABLE_A
WHERE RAC LIKE 'DS%'
AND OZNAKA IN ('X', 'Y')
GROUP BY SUBSTR(RAC, 7, 11), DATE
),
MAIN_DATA AS (
SELECT
DATE,
CIB,
SUM(END_DAY) AS MAIN_STANJE
FROM TABLE_A
WHERE DATE >= SYSDATE - 30
AND OZNAKA IN ('X', 'Y')
GROUP BY DATE, CIB
)
SELECT
main.DATE,
main.CIB,
main.MAIN_STANJE AS ORIGINAL_STANJE,
main.MAIN_STANJE + COALESCE(rac.RAC_STANJE, 0) AS STANJE_D_1,
(SELECT ROUND(AVG(sub.MAIN_STANJE + COALESCE(rac_avg.RAC_STANJE, 0)), 2)
FROM MAIN_DATA sub
LEFT JOIN RAC_SUM rac_avg
ON rac_avg.R_CIB = sub.CIB
AND rac_avg.DATE = sub.DATE
WHERE sub.DATE >= TRUNC(SYSDATE) - 30
AND sub.DATE < TRUNC(SYSDATE)
AND sub.CIB = main.CIB) AS AVG_30
FROM MAIN_DATA main
LEFT JOIN RAC_SUM rac
ON rac.R_CIB = main.CIB
AND rac.DATE = main.DATE
