Iterating thought dates in SQL

Question

I have a table 1 of data that looks a bit like that:

Record# Date        Person
1       12/12/2012  Tom
2       01/02/2013  Tom
3       10/02/2013  Tom
4       02/01/2013  John
5       04/01/2014  John
6       30/06/2010  Mary
7       30/06/2011  Mary
8       30/06/2012  Mary
9       30/07/2012  Mary

and another table 2 where we have the registration date of each Person

Person  RegisterDate   MaxRecord
Tom     15/12/2011     100
John    01/01/2013     10
Mary    16/06/2010     50

Before adding a record in the table1, I need to check whether the annual count of record (table1) by Person is lower than the MaxRecord number (table2) for that Person. By Annual, I mean startDate = registration date and endDate = registration date + 1 year and not from Januray 1st till December 31st.

If I want to add a record for Mary, I want to write SQL that will give me the following output:

StartDate   EndDate     CountRecord
16/06/2010  15/06/2011    1
16/06/2011  15/06/2012    1
16/06/2012  15/06/2013    2

Once this output is build, I could test whether thedate of a new record (for a Person) is allowed or not.

Could someone give me a clue, a link to a tutorial or some help please?

score 0 · Answer 1 · edited May 23 '17 at 12:29

For the following I am assuming you already have a numbers table, If you don't have a numbers table, then I'd recommend you make one then, but if you don't want to then you can create a number list on the fly

You can get a list of all boundaries by cross joining your table of Register dates (RegDate) with your numbers table:

SELECT  r.Person, 
        DATE_ADD(r.RegisterDate, INTERVAL n.Number YEAR) PeriodStart,
        DATE_ADD(r.RegisterDate, INTERVAL n.Number + 1 YEAR) PeriodEnd,
        n.Number
FROM    RegDate r
        CROSS JOIN Numbers n;

This gives a table like (Just for Tom and adding WHERE n.Number <= 3; as an example):

Person  PERIODSTART   PERIODEND   NUMBER
Tom     15/12/2011    15/12/2012    0
Tom     15/12/2012    15/12/2013    1
Tom     15/12/2013    15/12/2014    2
Tom     15/12/2014    15/12/2015    3

Example on SQL Fiddle

You then need to join this to your table of other dates (T) to do the count:

SELECT  r.Person,
        DATE_ADD(r.RegisterDate, INTERVAL n.Number YEAR) StartDate,
        DATE_ADD(DATE_ADD(r.RegisterDate, INTERVAL n.Number + 1 YEAR), INTERVAL -1 DAY) EndDate,
        COUNT(T.Record) AS `CountRecord`
FROM    RegDate r
        CROSS JOIN Numbers n
        LEFT JOIN T
            ON T.Person = r.Person
            AND T.Date >= DATE_ADD(r.RegisterDate, INTERVAL n.Number YEAR)
            AND T.Date < DATE_ADD(r.RegisterDate, INTERVAL n.Number + 1 YEAR)
WHERE   DATE_ADD(r.RegisterDate, INTERVAL n.Number YEAR) <= CURRENT_TIMESTAMP
AND     r.Person = 'Mary'
GROUP BY r.Person, R.RegisterDate, n.Number;

Giving a final result of:

PERSON  STARTDATE   ENDDATE     COUNTRECORD
Mary    2010-06-16  2011-06-15      1
Mary    2011-06-16  2012-06-15      2
Mary    2012-06-16  2013-06-15      1
Mary    2013-06-16  2014-06-15      0

Full Example on SQL-Fiddle

I have limited the results to where the StartDate is less than today using this line

WHERE   DATE_ADD(r.RegisterDate, INTERVAL n.Number YEAR) <= CURRENT_TIMESTAMP

you can obviously change this as you need

Hello, I come back to ask something. So far i was using a numbered list cretaed on the fly. But now, I'd like to optimize the db and use a numbers table as you have suggested. The issue is when i use that numbered table, the sql result is empty!!! don't know why. Here is the sql script — user2351564, Jun 09 '14 at 02:33
CREATE TABLE IF NOT EXISTS `ass_numbers` ( `SeqValue` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Contenu de la table `ass_numbers` -- INSERT INTO `ass_numbers` (`SeqValue`) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); — user2351564, Jun 09 '14 at 02:35

Iterating thought dates in SQL

1 Answers1