All Forums Database
amar8580 1 post Joined 04/15
06 Apr 2015
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'

dnoeth 4628 posts Joined 11/04
07 Apr 2015

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

 

Dieter

You must sign in to leave a comment.