Hi Forum,
I've written a Select Statement that uses a Start & End date that needs to change back one day for each loop.
My code starts at Line 05, I saw the code from line 1 - 4 and thought that would be good to replace where I''ve used 'Sysdate'.
My problem is that I can't get this code to run as it says it wasn't expecting 'SELECT'.
I realise I haven't included the end of the Loop code by the way.
Can anyone advise where I'm going wrong?
I need the Start & End date to change back to the previous date each loop.
I also need to collect the results for each 'TODAYDATE' and through until I have the results for
the past year .
The below is 1 day's results, Thanks
begin
START_DATE := TO_NUMBER(TO_CHAR(TO_DATE('2016-09-10', 'yyyy-MM-dd'), 'j'));
END_DATE := TO_NUMBER(TO_CHAR(TO_DATE('2016-09-12', 'yyyy-MM-dd'), 'j'));
for cur_r in start_date..end_date loop (
select DISTINCT
AC.TODAYDATE,
AC.TRANS_DATE,
AC.BUY_3_6_MTH_BUY,
SUM(AC.MEMBER_COUNT) as MEMBER_COUNT
FROM (select DISTINCT
AB.TODAYDATE,
AB.TRANS_DATE,
AB.BUY_3_6_MTH_BUY,
COUNT(distinct AB.MBR_IDNT) as MEMBER_COUNT
FROM (SELECT DISTINCT
TRUNC(sysdate) as TODAYDATE,
A1.TRANS_DATE as TRANS_DATE,
a1.MBR_IDNT as MBR_IDNT,
(case
when (TOTAL_SALES < 249.9999 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '<$250'
when (TOTAL_SALES between 250.00 and 499.9999 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '<$250-$500'
when (TOTAL_SALES between 500.00 and 749.9999 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '<$500-$750'
when (TOTAL_SALES between 750.00 and 999.9999 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '<$750-$1000'
when (TOTAL_SALES between 1000.00 and 1499.9999 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '<$1000-$1500'
when (TOTAL_SALES between 1500.00 and 1999.9999 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '<$1500-$2000'
when (TOTAL_SALES >= 2000.00 and TRANS_DATE between (TRUNC(sysdate)-367) and (TRUNC(sysdate)-2)) then '>$2000'
ELSE '' END) BUY_3_6_mth_Buy
from WY_3_6_MTH_LAPSED_2 a1
LEFT JOIN
(select distinct
MBR_IDNT
from WY_3_6_MTH_LAPSED_2
where (TRUNC(TRANS_DATE) = (TRUNC(sysdate)-90) or TRUNC(TRANS_DATE) = (TRUNC(sysdate)-180))
) AA
on AA.MBR_IDNT = A1.MBR_IDNT
where (TRUNC(TRANS_DATE) = (TRUNC(sysdate)-90) or TRUNC(TRANS_DATE) = (TRUNC(sysdate)-180))
) AB
where AB.BUY_3_6_MTH_BUY is not null
group by
AB.TODAYDATE,
AB.TRANS_DATE,
AB.BUY_3_6_MTH_BUY
)AC
GROUP BY
AC.TODAYDATE,
AC.TRANS_DATE,
AC.BUY_3_6_MTH_BUY
;