CODEWITHSUNDEEP
mysqlloopsstored-procedures
user3783243
user3783243

Reputation: 5224

Iterate Over Date Mysql Loop

I've written a stored procedure to iterate over every week for three years. It doesn't work though and returns a vague error message.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 18

DELIMITER $$
CREATE PROCEDURE loop_three_years()
BEGIN
    declare y INT default 2016;
    declare m int default 4;
    declare d int default 20;
    WHILE y <= 2019 DO
        WHILE YEARWEEK(concat(y, '-', m, '-', d)) <= 53 DO
            WHILE m < 12 DO
                WHILE (m = 2 and d <= 29) OR (d <=30 and m in(4, 6,9,11)) OR ( m in(1,3,5,7,8,10,12) AND d <= 31) DO
                    set d = d + 7;
                    SELECT YEARWEEK(concat(y, '-', m, '-', d));
                END WHILE;
                set d=1;
        END WHILE;
        set m = 1;
        SET y = y + 1;
    END WHILE;
END
$$

When I used this as minimal parts they work so I'm not sure what the issue is with my reassembly. Also not sure if there's a better way to do this. (The select is just for testing, it will be an insert when I use the real code.

Upvotes: 2

Views: 3648

Answers (1)

DRapp
DRapp

Reputation: 48139

Slightly Altered from a previous solution

You can build your own dynamic calendar / list using ANY other table in your system that has at least as many records as you need to fake row numbers. The query below will use MySQL @ variables which work like an inline program and declaration. I can start the list with a given date... such as your 2016-04-20 and then each iteration through, add 1 week using date-based functions. No need for me to know or care about how many days have a 28, 29(leap-year), 30 or 31 days.

The table reference below of "AnyTableThatHasAtLeast156Records" is just that.. Any table in your database that has at least 156 records (52 weeks per year, 3 years)

select
      YEARWEEK( @startDate ) WeekNum,
      @startDate as StartOfWeek,              
      @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
   from 
      ( select @startDate := '2016-04-20') sqlv,
      AnyTableThatHasAtLeast156Records
   limit
      156

This will give you a list of 156 records (provided your "anyTable…" has 156 records all at once. If you need to join this to some other transaction table, you could do so by making the above a JOIN table. Benefit here, Since I included the begin date and end of week, those can be part of your joining to table.

Example, on 

record   WeekNum   StartOfWeek   EndOfWeek
1        ??        2016-04-20    2016-04-27
2        ??        2016-04-27    2016-05-04
3        ??        2016-05-04    2016-05-11
4        ??        2016-04-11    2016-05-18... etc

By adding 1 week to the starting point, you can see that it would do Ex: Monday to Monday. And the JOIN Condition below I have LESS THAN the EndOfWeek. This would account for any transactions UP TO but not including the ending date... such as transactions on 2016-04-26 11:59:59PM (hence LESS than 2016-04-27, as 04/27 is the beginning of the next week's cycle of transactions)

select
      Cal.WeekNum,
      YT.YourColumns
   from
      YourTransactionTable YT
         JOIN ( aboveCalendarQuery ) Cal
            on YT.TransactionDate >= Cal.StartOfWeek
            AND YT.TransactionDate < Cal.EndOfWeek
   where
      whatever else

You could even do sum() with group by such as by WeekNum if that is what you intend.

Hopefully this is a much more accurate and efficient way to build out your calendar to run with and linking to transactions if you so needed to.

Response from comment.

You could by doing a join to a ( select 1 union select 2 union … select 156 ), but your choice. The ONLY reason for the "AnyTable…" is I am sure with any reasonable database with transactions you would have 156 records or more easily. It's sole purpose is to just allow a row for cycling through the iterations to dynamically create the rows.

Also much more sound than the looping mechanism you have run into to begin with. Nothing wrong with that, especially learning purposes, but if more efficient ways, doesn't that make more sense?

Per feedback from comment

I dont exactly know your other table you are trying to insert into, but yes, you can use this for all 3000 things. Provide more of what you are trying to do and I can adjust... In the mean-time, something like this...

insert into YourOtherTable
(   someField,
    AnotherField,
    WeekNum 
)
select
      x.someField,
      x.AnotherField,
      z.WeekNum
   from
      Your3000ThingTable x
         JOIN (select
                     YEARWEEK( @startDate ) WeekNum,
                     @startDate as StartOfWeek,              
                     @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
                  from 
                     ( select @startDate := '2016-04-20') sqlv,
                     AnyTableThatHasAtLeast156Records
                  limit
                     156 ) z
            on 1=1
   where
      x.SomeCodition...

By joining the the select of 156 records on 1=1 (which is always true), it will return 156 entries for whatever record is in the Your3000ThingTable. So, if you have an inventory item table with 

Item  Name
1     Thing1
2     Thing2
3     Thing3

Your final insert would be

Item   Name     WeekNum
1      Thing1   1
1      Thing1   2
1      Thing1   ...
1      Thing1   156
2      Thing2   1
2      Thing2   2
2      Thing2   ...
2      Thing2   156
3      Thing3   1
3      Thing3   2
3      Thing3   ...
3      Thing3   156

And to pre-confirm what you THINK would happen, just try the select/join on 1=1 and you'll see all the records the query WOULD be inserting into your destination table.

Upvotes: 3

Related Questions