ccoeder ccoeder - 8 months ago 44
MySQL Question

how to check given date is in range on table records

This is the problem i can not solve:
I have one special index number on transactions tables and i reset this special index number every year.Here is the code.

SELECT IFNULL(MAX(Index),0)+1 as Index_Number FROM Transactions WHERE YEAR(Create_Date) = '2016'

I have no problem with this query, its works fine.If we think that the dates of the recorded data are:

id | index | create_date
1 | 7 | 2016-01-01
2 | 8 | 2016-01-03

When data is entered in the date 2016-01-02, the index number of the id 2 must be 9. The index number of the newly added data must be 8.

In other words, the final state should be:

id | index | create_date
1 | 7 | 2016-01-01
3 | 8 | 2016-01-02
2 | 9 | 2016-01-03

How can I do this?

I have forgotten to mention that the index number varies according to each person so I can not use auto_increment.

Answer Source

According to your example it should be something like this:

set @userinput = '2016-01-02';
set @i = (SELECT ifnull(min(index),1) FROM FROM Transactions WHERE YEAR(Create_Date) = '2016') and Create_Date > @userinput;
update  Transactions set index = index+1 WHERE YEAR(Create_Date) = '2016') and  index >= @i;
insert into Transactions (`index`,`Create_Date`) values (@i, @userinput);

so first you search min index for dates that greater then entered (for the certain year),

then you update all "index" for the rows with greater date but same year, shifting it

finally insert you new row