partition by prior week

Database

partition by prior week

hi all

i am trying to create a sql statement that will bring back data for my current date and my previous week date. but its giving me the same values...here is the sql.

my prior_Wd_amt should have the value of prior week amount. so eg:

report dt   |    market_channel  |  prov_cd  | trust  |  amt | prior_Wd_amt

apr 6th      |             a                |  ON          | B         |  100 |       amount of march 30th

SELECT DISTINCT

 t1.REPORT_DT,

 t1.MARKETING_CHANNEL,

 t1.PROV_CD,

 t1.TRUST,

sum(t1.TOTAL_AMNT) OVER (PARTITION BY  t1.REPORT_DT, t1.MARKETING_CHANNEL, t1.PROV_CD,t1.TRUST) AS AMT,

sum(t1.TOTAL_AMNT) OVER (PARTITION BY  (t1.REPORT_DT - 7) , t1.MARKETING_CHANNEL, t1.PROV_CD,t1.TRUST)  AS PRIOR_WD_AMT

FROM daily_transactional_rev t1

WHERE t1.PROV_CD IN  ( 'ON', 'QC') AND t1.TRUST NOT = 'BRT'

1 REPLY
Senior Apprentice

Re: partition by prior week

Whatever calculation you put in PARTITION BY doesn't matter, it's still the same rows.

But you don't need a OLAP-function, it's a simple aggregate:

SELECT 
t1.REPORT_DT,
t1.MARKETING_CHANNEL,
t1.PROV_CD,
t1.TRUST,
sum(case when REPORT_DT = CURRENT_DATE THEN t1.TOTAL_AMNT END) AS AMT,
sum(case when REPORT_DT = CURRENT_DATE - 7 THEN t1.TOTAL_AMNT END) AS PRIOR_WD_AMT
FROM daily_transactional_rev t1
WHERE t1.PROV_CD IN ( 'ON', 'QC') AND t1.TRUST NOT = 'BRT'
AND (REPORT_DT = CURRENT_DATE - 7 OR REPORT_DT = CURRENT_DATE)
GROUP BY 1,2,3,4