#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    64
    Rep Power
    6

    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 | 2014-01-30        |
    +------------------------+
    | 31 | 2014-01-31        |
    +------------------------+
    | 32 | 2014-02-01        |
    +------------------------+
    | 33 | 2014-02-02        |
    +------------------------+
    (Etc...)
    Last edited by toronado455; January 10th, 2014 at 01:46 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    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 '2014-01-30' + INTERVAL n DAY
      FROM numbers
    and if you need more than 10 dates, you can either pre-load your numbers table with more numbers, or simply use cross joins of the numbers table in the SELECT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    64
    Rep Power
    6
    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 '2014-01-30' + INTERVAL n DAY
      FROM numbers
    and if you need more than 10 dates, you can either pre-load 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 09:38 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    while that works, you simply gotta stop thinking in terms of loops

    sql is all-at-once, not record-at-a-time

    forget loops -- you will thank me later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    64
    Rep Power
    6
    Originally Posted by r937
    while that works, you simply gotta stop thinking in terms of loops

    sql is all-at-once, not record-at-a-time

    forget loops -- you will thank me later
    OK, but it's a mystery to me how to "pre-load your numbers table with more numbers" other than what I did.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by toronado455
    OK, but it's a mystery to me how to "pre-load your numbers table with more numbers" other than what I did.
    let me explain the mystery

    drop your numbers table, and re-create 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 three-way cross join that generates 999 numbers

    granted, this isn't the way you "pre-load" 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    64
    Rep Power
    6
    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 three-way cross join that generates 999 numbers
    Can that be re-written as a two-way 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 12:37 AM.

IMN logo majestic logo threadwatch logo seochat tools logo