Hi,
I have a table that I want to run a Loop script on but I can't get the dates to change each Loop.
First up, here's my current attempt at the Loop & also the Create & Insert script.
I would like the 'l_TRANS_DATE' to change to the Previous date each Loop & all the results then are displayed.
Can anyone point out where / why this is wrong?
Thanks
declare
TRANS_DATE date; -- declare the variables
l_trans_date date;
begin
TRANS_DATE := TO_DATE('2016-09-01', 'yyyy-MM-dd'); -- give the variables a value
FOR i IN 1 - 20 LOOP -- start the 'For' Loop to run 20 Loops
select -- begin the Select statement
l_TRANS_DATE,
TOTAL_SALES,
SUM(MBR_IDNT) as MBR_COUNT
from INSERT_TEST_DATA
group by
TRANS_DATE,
TOTAL_SALES -- end the select statement
L_TRANS_DATE := TRANS_DATE - 1; -- Make l_TRANS_DATE the previous Days date
end LOOP; -- end Loop
DBMS_OUTPUT.PUT_LINE (i); -- Show Output Results
| create table INSERT_TEST_DATA (MBR_IDNT INT, TRANS_DATE DATE, TOTAL_SALES INT); | | |
--TRUNCATE table INSERT_TEST_DATA
INSERT INTO INSERT_TEST_DATA (MBR_IDNT, TRANS_DATE, TOTAL_SALES)
select 1,TO_DATE('06/03/16','DD/MM/YY'),56 from DUAL
union all select 2,TO_DATE('30/03/16','DD/MM/YY'),186 from DUAL
union all select 3,TO_DATE('13/09/14','DD/MM/YY'),32 from DUAL
union all select 4,TO_DATE('5/09/2014','DD/MM/YY'),89 from DUAL
union all select 5,TO_DATE('1/09/2014','DD/MM/YY'),2 from DUAL
union all select 6,TO_DATE('18/07/2015','DD/MM/YY'),31 from DUAL
union all select 7,TO_DATE('9/10/2014','DD/MM/YY'),57 from DUAL
union all select 8,TO_DATE('6/09/2016','DD/MM/YY'),121 from DUAL
union all select 9,TO_DATE('9/06/2016','DD/MM/YY'),35 from DUAL
union all select 10,TO_DATE('3/06/2016','DD/MM/YY'),188 from DUAL
union all select 11,TO_DATE('5/03/2015','DD/MM/YY'),113 from DUAL
union all select 12,TO_DATE('22/01/2015','DD/MM/YY'),117 from DUAL
union all select 13,TO_DATE('21/06/2015','DD/MM/YY'),80 from DUAL
union all select 14,TO_DATE('27/07/2016','DD/MM/YY'),163 from DUAL
union all select 15,TO_DATE('4/02/2015','DD/MM/YY'),194 from DUAL
union all select 16,TO_DATE('3/09/2016','DD/MM/YY'),197 from DUAL
union all select 17,TO_DATE('31/01/2015','DD/MM/YY'),173 from DUAL
union all select 18,TO_DATE('24/05/2016','DD/MM/YY'),161 from DUAL
union all select 19,TO_DATE('27/07/2016','DD/MM/YY'),36 from DUAL
union all select 20,TO_DATE('3/06/2015','DD/MM/YY'),61 from DUAL
union all select 21,TO_DATE('9/09/2015','DD/MM/YY'),198 from DUAL
union all select 22,TO_DATE('26/12/2014','DD/MM/YY'),82 from DUAL
union all select 23,TO_DATE('18/11/2015','DD/MM/YY'),77 from DUAL
union all select 24,TO_DATE('27/12/2015','DD/MM/YY'),161 from DUAL
union all select 25,TO_DATE('9/03/2015','DD/MM/YY'),84 from DUAL
union all select 26,TO_DATE('24/02/2015','DD/MM/YY'),167 from DUAL
union all select 27,TO_DATE('14/08/2016','DD/MM/YY'),89 from DUAL
union all select 28,TO_DATE('4/08/2016','DD/MM/YY'),135 from DUAL
union all select 29,TO_DATE('31/08/2016','DD/MM/YY'),149 from DUAL
union all select 30,TO_DATE('20/06/2015','DD/MM/YY'),153 from DUAL
union all select 31,TO_DATE('18/06/2016','DD/MM/YY'),98 from DUAL
union all select 32,TO_DATE('5/09/2016','DD/MM/YY'),199 from DUAL
union all select 33,TO_DATE('6/03/2015','DD/MM/YY'),138 from DUAL
union all select 34,TO_DATE('3/08/2016','DD/MM/YY'),15 from DUAL
union all select 35,TO_DATE('8/05/2015','DD/MM/YY'),117 from DUAL
union all select 36,TO_DATE('23/10/2014','DD/MM/YY'),140 from DUAL
union all select 37,TO_DATE('4/10/2015','DD/MM/YY'),114 from DUAL
union all select 38,TO_DATE('17/06/2015','DD/MM/YY'),26 from DUAL
union all select 39,TO_DATE('31/12/2015','DD/MM/YY'),129 from DUAL
union all select 40,TO_DATE('24/10/2015','DD/MM/YY'),163 from DUAL
union all select 41,TO_DATE('29/11/2015','DD/MM/YY'),192 from DUAL
union all select 42,TO_DATE('11/05/2015','DD/MM/YY'),47 from DUAL
union all select 43,TO_DATE('24/05/2015','DD/MM/YY'),185 from DUAL
union all select 44,TO_DATE('16/08/2016','DD/MM/YY'),33 from DUAL
union all select 45,TO_DATE('15/08/2016','DD/MM/YY'),135 from DUAL
union all select 46,TO_DATE('23/08/2016','DD/MM/YY'),1 from DUAL
union all select 47,TO_DATE('26/05/2015','DD/MM/YY'),103 from DUAL
union all select 48,TO_DATE('23/06/2015','DD/MM/YY'),43 from DUAL
union all select 49,TO_DATE('9/06/2016','DD/MM/YY'),101 from DUAL
union all select 50,TO_DATE('23/11/2015','DD/MM/YY'),13 from DUAL
union all select 51,TO_DATE('4/01/2016','DD/MM/YY'),83 from DUAL
union all select 52,TO_DATE('7/09/2016','DD/MM/YY'),16 from DUAL
union all select 53,TO_DATE('8/09/2016','DD/MM/YY'),56 from DUAL
union all select 54,TO_DATE('2/01/2016','DD/MM/YY'),106 from DUAL
union all select 55,TO_DATE('5/06/2016','DD/MM/YY'),130 from DUAL
union all select 56,TO_DATE('17/06/2016','DD/MM/YY'),32 from DUAL
union all select 57,TO_DATE('10/06/2015','DD/MM/YY'),34 from DUAL
union all select 58,TO_DATE('6/09/2016','DD/MM/YY'),7 from DUAL
union all select 59,TO_DATE('29/08/2016','DD/MM/YY'),6 from DUAL
union all select 60,TO_DATE('9/05/2016','DD/MM/YY'),0 from DUAL
union all select 61,TO_DATE('16/01/2016','DD/MM/YY'),109 from DUAL
union all select 62,TO_DATE('18/06/2015','DD/MM/YY'),165 from DUAL
union all select 63,TO_DATE('6/03/2015','DD/MM/YY'),177 from DUAL
union all select 64,TO_DATE('19/06/2015','DD/MM/YY'),33 from DUAL
union all select 65,TO_DATE('4/06/2016','DD/MM/YY'),64 from DUAL
union all select 66,TO_DATE('7/01/2016','DD/MM/YY'),193 from DUAL
union all select 67,TO_DATE('29/10/2015','DD/MM/YY'),61 from DUAL
union all select 68,TO_DATE('21/08/2016','DD/MM/YY'),194 from DUAL
union all select 69,TO_DATE('29/05/2016','DD/MM/YY'),183 from DUAL
union all select 70,TO_DATE('22/06/2016','DD/MM/YY'),146 from DUAL
union all select 71,TO_DATE('30/11/2015','DD/MM/YY'),55 from DUAL
union all select 72,TO_DATE('22/05/2015','DD/MM/YY'),47 from DUAL
union all select 73,TO_DATE('10/04/2015','DD/MM/YY'),52 from DUAL
union all select 74,TO_DATE('3/09/2015','DD/MM/YY'),151 from DUAL
union all select 75,TO_DATE('12/09/2016','DD/MM/YY'),144 from DUAL
union all select 76,TO_DATE('3/07/2016','DD/MM/YY'),29 from DUAL
union all select 77,TO_DATE('24/08/2015','DD/MM/YY'),122 from DUAL
union all select 78,TO_DATE('16/10/2015','DD/MM/YY'),181 from DUAL
union all select 79,TO_DATE('18/04/2015','DD/MM/YY'),83 from DUAL
union all select 80,TO_DATE('11/12/2014','DD/MM/YY'),84 from DUAL
union all select 81,TO_DATE('5/11/2014','DD/MM/YY'),155 from DUAL
union all select 82,TO_DATE('17/02/2015','DD/MM/YY'),124 from DUAL
union all select 83,TO_DATE('18/12/2015','DD/MM/YY'),167 from DUAL
union all select 84,TO_DATE('28/05/2016','DD/MM/YY'),141 from DUAL
union all select 85,TO_DATE('24/10/2015','DD/MM/YY'),112 from DUAL
union all select 86,TO_DATE('23/12/2015','DD/MM/YY'),75 from DUAL
union all select 87,TO_DATE('18/12/2015','DD/MM/YY'),34 from DUAL
union all select 88,TO_DATE('21/10/2015','DD/MM/YY'),114 from DUAL
union all select 89,TO_DATE('18/02/2015','DD/MM/YY'),133 from DUAL
union all select 90,TO_DATE('22/05/2016','DD/MM/YY'),102 from DUAL
union all select 91,TO_DATE('29/01/2015','DD/MM/YY'),26 from DUAL
union all select 92,TO_DATE('9/02/2015','DD/MM/YY'),165 from DUAL
;
declare
TRANS_DATE date; -- declare the variables
l_trans_date date;
begin
TRANS_DATE := TO_DATE('2016-09-01', 'yyyy-MM-dd'); -- give the variables a value
FOR i IN 1 - 20 LOOP -- start the 'For' Loop to run 20 Loops
select -- begin the Select statement
l_TRANS_DATE,
TOTAL_SALES,
SUM(MBR_IDNT) as MBR_COUNT
from INSERT_TEST_DATA
group by
TRANS_DATE,
TOTAL_SALES -- end the select statement
L_TRANS_DATE := TRANS_DATE - 1; -- Make l_TRANS_DATE the previous Days date
end LOOP; -- end Loop
DBMS_OUTPUT.PUT_LINE (i); -- Show Output Results