January 10th, 2014, 01:49 AM

need loop for seqential records with dates
Code:
CREATE TABLE `datetable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datecol` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1'
How can I write (in MySQL) a loop to insert into this table records with seqential dates so that I end up with data like:
Code:
+++
 id  datecol 
++
 30  20140130 
++
 31  20140131 
++
 32  20140201 
++
 33  20140202 
++
(Etc...)
Last edited by toronado455; January 10th, 2014 at 02:46 AM.
January 10th, 2014, 07:11 AM

start with this 
Code:
CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers ( n ) VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
then you can create your dates like this 
Code:
INSERT INTO datetable ( datecol )
SELECT '20140130' + INTERVAL n DAY
FROM numbers
and if you need more than 10 dates, you can either preload your numbers table with more numbers, or simply use cross joins of the numbers table in the SELECT
January 10th, 2014, 09:37 AM

Originally Posted by r937
start with this 
Code:
CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers ( n ) VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
then you can create your dates like this 
Code:
INSERT INTO datetable ( datecol )
SELECT '20140130' + INTERVAL n DAY
FROM numbers
and if you need more than 10 dates, you can either preload your numbers table with more numbers, or simply use cross joins of the numbers table in the SELECT
Thank you, this works.
However, if your objective is to elliminate the need for a loop to generate the data in the datetable, I then instead need a loop to generate the numbers for the numbers table. (I don't know how to use the cross join).
I used this:
Code:
DELIMITER $$
CREATE PROCEDURE create_numbers()
BEGIN
DECLARE a INT Default 1 ;
simple_loop: LOOP
insert into numbers (n)
values(a);
SET a=a+1;
IF a=365 THEN
LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END $$
Last edited by toronado455; January 10th, 2014 at 10:38 AM.
January 10th, 2014, 01:44 PM

while that works, you simply gotta stop thinking in terms of loops
sql is allatonce, not recordatatime
forget loops  you will thank me later
January 10th, 2014, 07:15 PM

Originally Posted by r937
while that works, you simply gotta stop thinking in terms of loops
sql is allatonce, not recordatatime
forget loops  you will thank me later
OK, but it's a mystery to me how to "preload your numbers table with more numbers" other than what I did.
January 10th, 2014, 08:35 PM

Originally Posted by toronado455
OK, but it's a mystery to me how to "preload your numbers table with more numbers" other than what I did.
let me explain the mystery
drop your numbers table, and recreate it like above, with only the numbers from 0 through 9
then run this 
Code:
SELECT 100*hundreds.n +
10*tens.n +
units.n AS nnn
FROM numbers AS hundreds
CROSS
JOIN numbers AS tens
CROSS
JOIN numbers AS units
ORDER
BY nnn
this is a threeway cross join that generates 999 numbers
granted, this isn't the way you "preload" the numbers table with a load of numbers, it is an example of pulling more numbers than the simple 9 that are in the table
January 13th, 2014, 01:03 AM

Originally Posted by r937
Code:
SELECT 100*hundreds.n +
10*tens.n +
units.n AS nnn
FROM numbers AS hundreds
CROSS
JOIN numbers AS tens
CROSS
JOIN numbers AS units
ORDER
BY nnn
this is a threeway cross join that generates 999 numbers
Can that be rewritten as a twoway that generates 99 numbers? I have been trying to figure it out.
EDIT:
Nevermind, I just got it.
Code:
SELECT 10*tens.n +
units.n AS nn
FROM numbers AS tens
CROSS
JOIN numbers AS units
ORDER
BY nn
Last edited by toronado455; January 13th, 2014 at 01:37 AM.