5

My company has different type of invoices. Example:

H00001/2013 . . . H99999/2013

and

T00001/2013 . . . T99999/2013

The problem is, the numbering is increasing for new year. How can I make auto increment value reset for each new year?

This is my current code:

    CREATE TABLE `invoices` ( 
      `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
      `invoicetype` enum('A','B') NOT NULL, 
      `date` date NOT NULL, 
      `client` varchar(100) NOT NULL,
      PRIMARY KEY (invoicetype,invoicenumber)
    ) COMMENT='' ENGINE='MyISAM';
user2862056
  • 51
  • 1
  • 1
  • 3
  • 1
    Duplicate http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql – BMN Oct 09 '13 at 09:34

4 Answers4

6

Hey if you are using any client application for database like MysqlWorkBench or MYSQL Query Browser then you can do below steps to set AutoIncrement no -

  • Right click on Table and go to Alter Table
  • Select Options tab
  • Under that you can find Auto Increment label there you can reset the number.
Dharm
  • 87
  • 1
  • 11
5

You can reset auto increment value by using the ALTER TABLE statement. The syntax of the ALTER TABLE statement to reset auto increment value is as follows:

ALTER TABLE table_name AUTO_INCREMENT = VALUE;

EDITED:

If you don't want to run this query every year then you have other two option to do such thing as I am aware of this two.

  1. Create cron job/windows scheduled job
  2. As you are using MySql then there is Event Scheduler (Keep in mind this is added in MySql 5.1.6 not in previous versions of MySql)
Satyam Koyani
  • 4,236
  • 2
  • 22
  • 48
4

In your DB table, you can reset the counter with the following code:

ALTER TABLE tablename AUTO_INCREMENT = 1
Maizied Hasan Majumder
  • 1,197
  • 1
  • 12
  • 25
0

Perhaps you already found a solution and the answer I'm giving may not be useful, since it's been seven months.
Returning to the topic, I noticed that you have a composed primary key with two columns (invoicetype,invoicenumber). So there can be no duplicates of pairs invoicetype,invoicenumber . If you reset the auto_increment every year there might be possible to have duplicate pairs like 'A',1 for two rows, one invoice from year 2013 and the other from year 2014. So you can eliminate that primary key to prevent the violation of the primary key constraint. You can instead define a primary key (any decent table has one) with an auto_incremented column to make every row unique. Then you can define an auto incrementation mechanism for the invoicenumber column (I will shall return to this issue).
First I would define the invoice table like this: 

CREATE TABLE `invoices` (
  `id` int unsigned NOT NULL auto_increment,
  `invoicenumber` mediumint unsigned NOT NULL, 
  `invoicetype` enum('A','B') NOT NULL, 
  `invoicedate` date NOT NULL, -- not recomended to use reserved words for column names like date
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (id)
) COMMENT='' ENGINE='MyISAM';

Then I would define another table list_id :

CREATE TABLE `list_id` ( 
  `id` int unsigned NOT NULL auto_increment, 
  `id_inc` int unsigned NOT NULL, -- number of invoice
  `the_year` date NOT NULL, -- year corresponding to the number of invoice
  PRIMARY KEY (id)
) COMMENT='' ENGINE='MyISAM';

The above table can be used to set the value of invoicenumber for the current row inserted in invoice table (1 if it is the first invoice of the year of invoicedate, the maximum value of id_inc (coresponding to the year of invoicedate) plus one, otherwise). The rows are completed using a trigger of type before insert for the invoice table. So, before I insert a new invoice, I have to determine the value of invoicenumber. It will be 1, if there are no records in table list_id with column the_year having the value equal to the year of the new invoice. In this case I can insert in table list_id a new record with the values (1,2014) (id_inc,year). It will be the maximum value of id_inc plus 1, if there are record(s) in table list_id with column the_year having the value equal to the year of the new invoice. In this case I can insert in table list_id a new record with the values (7,2014) (id_inc,year). The trigger looks like this:

CREATE TRIGGER `increment_or_reset_new_year` 
BEFORE INSERT ON `invoices` 
FOR EACH ROW
thisTrigger : begin
declare new_id_year int(11);
declare nr_invoices_year int(11);
declare new_invoice_begin int(11);
declare current_year_row int(11);


set current_year_row = year(new.invoice_date);

set nr_invoices_year = get_nr_invoices_year(current_year_row);

if(get_nr_invoices_year(current_year_row) < 1) then

set new.invoicenumber = 1;
insert into list_id(id_inc,the_year) values (1,current_year_row);
leave thisTrigger;
end if;

if(get_nr_invoices_year(current_year_row) >= 1) then
set new.invoicenumber = get_max_id(year(new.invoice_date)) + 1;
set new_id_year = get_max_id(year(new.invoice_date)) + 1;
insert into list_id(id_inc,the_year) values(new_id_year,year(new.invoice_date));

end if;
end;

There are 2 functions in the trigger. The first one determines the number of rows from the list_id table having the_year equal with the current invoice year (given as parameter):

create function get_nr_invoices_year(invoice_year int) returns int
begin

declare nr_invoices_year int(11);

select count(*) into nr_invoices_year from lista_id where the_year = invoice_year;

return nr_invoices_year;

end;

The second one determines the maximum value of id_inc from table list_id which has the coresponding value of the_year equal with the year of current invoice (given as parameter):

create function get_max_id(year_invoice int) returns int
begin

declare max_id_year int(11);

select max(id_inc) into max_id_year from invoices.lista_id where the_year =year_invoice;

return max_id_year;

end;

So I can have one trigger, two functions and one table which controls the incrementation of invoicenumber.
Hope this helps! It worked for me!

zxcvbnm
  • 55
  • 11