Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Loop through dates

OscarBootsSep 17 2016 — edited Sep 18 2016

Hi Forum,

I need to Loop through a table changing the 'TRANS_DATE' each loop with the below method rather than use the 'connect < 20' method.

So can anyone help me get this below code to work?

The below is my attempt at what might work but is giving multiple errors.

Thanks

  1. declare    
  2.   TRANS_DATE   date;  -- declare the variables  
  3.   l_trans_date date;   
  4. begin    
  5.   TRANS_DATE := TO_DATE('2016-09-01', 'yyyy-MM-dd');  -- give the variables a value  
  6. FOR i IN 1 - 20 LOOP  -- start the 'For' Loop to run 20 Loops   
  7. select        -- begin the Select statement  
  8. l_TRANS_DATE,  
  9. TOTAL_SALES,  
  10. SUM(MBR_IDNT) as MBR_COUNT  
  11. from INSERT_TEST_DATA  
  12. group by  
  13. TRANS_DATE,  
  14. TOTAL_SALES        -- end the select statement  
  15. L_TRANS_DATE := TRANS_DATE - 1;  -- Make l_TRANS_DATE the previous Days date   
  16. end LOOP;           -- end Loop  
  17. DBMS_OUTPUT.PUT_LINE (i);      -- Show Output Results  
  1. --create table WY_3_6_MTH_LAPSED_2 (MBR_IDNT INT,  TRANS_DATE DATE, TOTAL_SALES INT);  
  2.   
  3.   
  4.   
  5.   
  6.   
  7.   
  8.   
  9.   
  10.   
  11.   
  12.   
  13.   
  14.   
  15.   
  16. --TRUNCATE table WY_3_6_MTH_LAPSED_2  
  17.   
  18.   
  19.   
  20.   
  21.   
  22.   
  23.   
  24.   
  25.   
  26.   
  27.   
  28.   
  29.   
  30.   
  31.   
  32.   
  33.   
  34.   
  35.   
  36.   
  37.   
  38.   
  39.   
  40. INSERT INTO WY_3_6_MTH_LAPSED_2 (MBR_IDNT, TRANS_DATE, TOTAL_SALES)  
  41.   
  42.   
  43.   
  44.   
  45.   
  46.   
  47.   
  48.   
  49.   
  50.   
  51.   
  52.   
  53.   
  54.   
  55.   
  56.   
  57.   
  58.   
  59.   
  60.   
  61.   
  62.   
  63.   
  64.   
  65. select 1,TO_DATE('06/03/16','DD/MM/YY'),56 from DUAL  
  66. union all select 2,TO_DATE('30/03/16','DD/MM/YY'),186 from DUAL  
  67. union all select 3,TO_DATE('13/09/14','DD/MM/YY'),32 from DUAL  
  68. union all select 4,TO_DATE('5/09/2014','DD/MM/YY'),89 from DUAL  
  69. union all select 5,TO_DATE('1/09/2014','DD/MM/YY'),2 from DUAL  
  70. union all select 6,TO_DATE('18/07/2015','DD/MM/YY'),31 from DUAL  
  71. union all select 7,TO_DATE('9/10/2014','DD/MM/YY'),57 from DUAL  
  72. union all select 8,TO_DATE('6/09/2016','DD/MM/YY'),121 from DUAL  
  73. union all select 9,TO_DATE('9/06/2016','DD/MM/YY'),35 from DUAL  
  74. union all select 10,TO_DATE('3/06/2016','DD/MM/YY'),188 from DUAL  
  75. union all select 11,TO_DATE('5/03/2015','DD/MM/YY'),113 from DUAL  
  76. union all select 12,TO_DATE('22/01/2015','DD/MM/YY'),117 from DUAL  
  77. union all select 13,TO_DATE('21/06/2015','DD/MM/YY'),80 from DUAL  
  78. union all select 14,TO_DATE('27/07/2016','DD/MM/YY'),163 from DUAL  
  79. union all select 15,TO_DATE('4/02/2015','DD/MM/YY'),194 from DUAL  
  80. union all select 16,TO_DATE('3/09/2016','DD/MM/YY'),197 from DUAL  
  81. union all select 17,TO_DATE('31/01/2015','DD/MM/YY'),173 from DUAL  
  82. union all select 18,TO_DATE('24/05/2016','DD/MM/YY'),161 from DUAL  
  83. union all select 19,TO_DATE('27/07/2016','DD/MM/YY'),36 from DUAL  
  84. union all select 20,TO_DATE('3/06/2015','DD/MM/YY'),61 from DUAL  
  85. union all select 21,TO_DATE('9/09/2015','DD/MM/YY'),198 from DUAL  
  86. union all select 22,TO_DATE('26/12/2014','DD/MM/YY'),82 from DUAL  
  87. union all select 23,TO_DATE('18/11/2015','DD/MM/YY'),77 from DUAL  
  88. union all select 24,TO_DATE('27/12/2015','DD/MM/YY'),161 from DUAL  
  89. union all select 25,TO_DATE('9/03/2015','DD/MM/YY'),84 from DUAL  
  90. union all select 26,TO_DATE('24/02/2015','DD/MM/YY'),167 from DUAL  
  91. union all select 27,TO_DATE('14/08/2016','DD/MM/YY'),89 from DUAL  
  92. union all select 28,TO_DATE('4/08/2016','DD/MM/YY'),135 from DUAL  
  93. union all select 29,TO_DATE('31/08/2016','DD/MM/YY'),149 from DUAL  
  94. union all select 30,TO_DATE('20/06/2015','DD/MM/YY'),153 from DUAL  
  95. union all select 31,TO_DATE('18/06/2016','DD/MM/YY'),98 from DUAL  
  96. union all select 32,TO_DATE('5/09/2016','DD/MM/YY'),199 from DUAL  
  97. union all select 33,TO_DATE('6/03/2015','DD/MM/YY'),138 from DUAL  
  98. union all select 34,TO_DATE('3/08/2016','DD/MM/YY'),15 from DUAL  
  99. union all select 35,TO_DATE('8/05/2015','DD/MM/YY'),117 from DUAL  
  100. union all select 36,TO_DATE('23/10/2014','DD/MM/YY'),140 from DUAL  
  101. union all select 37,TO_DATE('4/10/2015','DD/MM/YY'),114 from DUAL  
  102. union all select 38,TO_DATE('17/06/2015','DD/MM/YY'),26 from DUAL  
  103. union all select 39,TO_DATE('31/12/2015','DD/MM/YY'),129 from DUAL  
  104. union all select 40,TO_DATE('24/10/2015','DD/MM/YY'),163 from DUAL  
  105. union all select 41,TO_DATE('29/11/2015','DD/MM/YY'),192 from DUAL  
  106. union all select 42,TO_DATE('11/05/2015','DD/MM/YY'),47 from DUAL  
  107. union all select 43,TO_DATE('24/05/2015','DD/MM/YY'),185 from DUAL  
  108. union all select 44,TO_DATE('16/08/2016','DD/MM/YY'),33 from DUAL  
  109. union all select 45,TO_DATE('15/08/2016','DD/MM/YY'),135 from DUAL  
  110. union all select 46,TO_DATE('23/08/2016','DD/MM/YY'),1 from DUAL  
  111. union all select 47,TO_DATE('26/05/2015','DD/MM/YY'),103 from DUAL  
  112. union all select 48,TO_DATE('23/06/2015','DD/MM/YY'),43 from DUAL  
  113. union all select 49,TO_DATE('9/06/2016','DD/MM/YY'),101 from DUAL  
  114. union all select 50,TO_DATE('23/11/2015','DD/MM/YY'),13 from DUAL  
  115. union all select 51,TO_DATE('4/01/2016','DD/MM/YY'),83 from DUAL  
  116. union all select 52,TO_DATE('7/09/2016','DD/MM/YY'),16 from DUAL  
  117. union all select 53,TO_DATE('8/09/2016','DD/MM/YY'),56 from DUAL  
  118. union all select 54,TO_DATE('2/01/2016','DD/MM/YY'),106 from DUAL  
  119. union all select 55,TO_DATE('5/06/2016','DD/MM/YY'),130 from DUAL  
  120. union all select 56,TO_DATE('17/06/2016','DD/MM/YY'),32 from DUAL  
  121. union all select 57,TO_DATE('10/06/2015','DD/MM/YY'),34 from DUAL  
  122. union all select 58,TO_DATE('6/09/2016','DD/MM/YY'),7 from DUAL  
  123. union all select 59,TO_DATE('29/08/2016','DD/MM/YY'),6 from DUAL  
  124. union all select 60,TO_DATE('9/05/2016','DD/MM/YY'),0 from DUAL  
  125. union all select 61,TO_DATE('16/01/2016','DD/MM/YY'),109 from DUAL  
  126. union all select 62,TO_DATE('18/06/2015','DD/MM/YY'),165 from DUAL  
  127. union all select 63,TO_DATE('6/03/2015','DD/MM/YY'),177 from DUAL  
  128. union all select 64,TO_DATE('19/06/2015','DD/MM/YY'),33 from DUAL  
  129. union all select 65,TO_DATE('4/06/2016','DD/MM/YY'),64 from DUAL  
  130. union all select 66,TO_DATE('7/01/2016','DD/MM/YY'),193 from DUAL  
  131. union all select 67,TO_DATE('29/10/2015','DD/MM/YY'),61 from DUAL  
  132. union all select 68,TO_DATE('21/08/2016','DD/MM/YY'),194 from DUAL  
  133. union all select 69,TO_DATE('29/05/2016','DD/MM/YY'),183 from DUAL  
  134. union all select 70,TO_DATE('22/06/2016','DD/MM/YY'),146 from DUAL  
  135. union all select 71,TO_DATE('30/11/2015','DD/MM/YY'),55 from DUAL  
  136. union all select 72,TO_DATE('22/05/2015','DD/MM/YY'),47 from DUAL  
  137. union all select 73,TO_DATE('10/04/2015','DD/MM/YY'),52 from DUAL  
  138. union all select 74,TO_DATE('3/09/2015','DD/MM/YY'),151 from DUAL  
  139. union all select 75,TO_DATE('12/09/2016','DD/MM/YY'),144 from DUAL  
  140. union all select 76,TO_DATE('3/07/2016','DD/MM/YY'),29 from DUAL  
  141. union all select 77,TO_DATE('24/08/2015','DD/MM/YY'),122 from DUAL  
  142. union all select 78,TO_DATE('16/10/2015','DD/MM/YY'),181 from DUAL  
  143. union all select 79,TO_DATE('18/04/2015','DD/MM/YY'),83 from DUAL  
  144. union all select 80,TO_DATE('11/12/2014','DD/MM/YY'),84 from DUAL  
  145. union all select 81,TO_DATE('5/11/2014','DD/MM/YY'),155 from DUAL  
  146. union all select 82,TO_DATE('17/02/2015','DD/MM/YY'),124 from DUAL  
  147. union all select 83,TO_DATE('18/12/2015','DD/MM/YY'),167 from DUAL  
  148. union all select 84,TO_DATE('28/05/2016','DD/MM/YY'),141 from DUAL  
  149. union all select 85,TO_DATE('24/10/2015','DD/MM/YY'),112 from DUAL  
  150. union all select 86,TO_DATE('23/12/2015','DD/MM/YY'),75 from DUAL  
  151. union all select 87,TO_DATE('18/12/2015','DD/MM/YY'),34 from DUAL  
  152. union all select 88,TO_DATE('21/10/2015','DD/MM/YY'),114 from DUAL  
  153. union all select 89,TO_DATE('18/02/2015','DD/MM/YY'),133 from DUAL  
  154. union all select 90,TO_DATE('22/05/2016','DD/MM/YY'),102 from DUAL  
  155. union all select 91,TO_DATE('29/01/2015','DD/MM/YY'),26 from DUAL  
  156. union all select 92,TO_DATE('9/02/2015','DD/MM/YY'),165 from DUAL  
  157. ;
This post has been answered by CarlosDLG on Sep 17 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 16 2016
Added on Sep 17 2016
20 comments
21,928 views