Solved

MYSQL 'insert record' Loop over days and weeks

Posted on 2014-12-16
2
56 Views
Last Modified: 2015-11-01
I am trying to automate the entering of records into a table, nothing too fancy table wise

ID (primary, auto increment)
UserID (foreign key to a user table)
Date (dd-mm-yyyy)
Notes (blank by default)

In a nutshell I want to write a small routine that will auto populate this table, preferably (for now) just using MYSQL, though this COULD be done in PHP if required

I want to take a date (for example Mon 2nd Feb 2015) and automate a 'routine' for a user by automatically adding records for a user ID which will form a 'on call' rota.

Example one group of users has a routine of

Week 1 - MON,TUE,WED,THU
Week 2 - FRI, SAT. SUN
Week 3 - OFF
Week 4 - OFF
Week 5 - OFF
Week 6 - MON, TUE, WED, THU
Week 7 - FRI, SAT, SUN

This is rigid and the formula doesn't change so user 1 will follow the exact routine above user 2 will start the routine one week later.. i,e

Week 1 - OFF
Week 2 - MON,TUE,WED,THU
Week 3 - FRI, SAT. SUN
Week 4 - OFF
Week 5 - OFF
Week 6 - OFF
Week 7 - MON, TUE, WED, THU

So in the instance of user one, it will add a record for

(Feb) 2nd, 3rd, 4th, 5th, 13th,14th,15th,
(Mar) 9th, 11th, 12th, 13th

If someone could demonstrate how this can be done (with just ONE user) then I will be able to use that knowledge to get started on the rest and the other rotas I need to generate (1 full week in 3 etc etc). With the addition of MYSQL SCHEDULES, this makes searching for help on this subject a bit tricky.

Many thanks for any help getting going on this area of MYSQL / Dates and Loops
0
Comment
Question by:mvwmail
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40503810
MySQL lacks recursive CTEs. You will not be able to do it with a single query. MySQL has support for Stored Procedures. In my opinion it will be easy to write one
0
 
LVL 3

Author Closing Comment

by:mvwmail
ID: 41166764
And thats what I did, thanks, sorry for long delay in closure
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 37
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 33
SQL Server - Set Value of Multiple Fields in One Query 10 25
Run SQL Server Proc from Access 11 32
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question