2

A JobID goes as follows: ALC-YYYYMMDD-001. The first three are a companies initials, the last three are an incrementing number that resets daily and increments throughout the day as jobs are added for a maximum of 999 jobs in a day; it is these last three that I am trying to work with.

I am trying to get a before-insert trigger to look for the max JobID of the day, and add one so I can have the trigger derive the proper JobID. For the first job, it will of course return null. So here is what I have so far.

Through the following I can get a result of '000'.

set @maxjobID = 
 (select SUBSTRING(
  (Select MAX(
   SUBSTRING((Select JobID FROM jobs WHERE SUBSTRING(JobID,5,8)=date_format(curdate(), '%Y%m%d')),4,12)
             )
  ),14,3)
 );

select lpad((select ifnull(@maxjobID,0)),3,'0')

But I really need to add one to this keeping the leading zeros to increment the first and subsequent jobs of the day. My problem is as soon as try to add '1' I get a return of 'BLOB'. That is:

select lpad((select ifnull(@maxjobID,0)+1),3,'0')

returns 'BLOB'

I need it to return '001' so I can concatenate that result with the CO initials and the current date.

Improve this question
1
  • The tables are MyISAM if that helps. Commented Nov 18, 2012 at 18:51

3 Answers 3

Reset to default
1

try casting VARCHAR back to INTEGER

SELECT lpad(SELECT (COALESCE(@maxjobID,0, CAST(@maxjobID AS SIGNED)) + 1),3,'0')
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Still getting a return of "BLOB" on this one. I tried messing with CAST and CONVERT before posting, but to no avail.
0

If you're using the MyISAM storage engine, you can implement exactly this with AUTO_INCREMENT, without denormalising your data into a delimited string:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

In your case:

  1. Normalise your schema:

    ALTER TABLE jobs
  ADD initials CHAR(3)              NOT NULL FIRST,
  ADD date     DATE                 NOT NULL AFTER initials,
  ADD seq      SMALLINT(3) UNSIGNED NOT NULL AFTER date,
;

  2. Normalise your existing data:

    UPDATE jobs SET
  initials = SUBSTRING_INDEX(JobID, '-',  1),
  date     = STR_TO_DATE(SUBSTRING(JobID, 5, 8), '%Y%m%d'),
  seq      = SUBSTRING_INDEX(JobID, '-', -1)
;

  3. Set up the AUTO_INCREMENT:

    ALTER TABLE jobs
  DROP PRIMARY KEY,
  DROP JobID,
  MODIFY seq SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY(initials, date, seq)
;

You can then recreate your JobID as required on SELECT (or even create a view from such a query):

SELECT CONCAT_WS(
         '-',
         initials,
         DATE_FORMAT(date, '%Y%m%d'),
         LPAD(seq, 3, '0')
       ) AS JobID,
       -- etc.

If you're using InnoDB, whilst you can't generate sequence numbers in this fashion I'd still recommend normalising your data as above.

Improve this answer

4 Comments

I fought with the idea of normalizing as you mention. From my understanding though this would make the JobID a compound primary key which by itself isn't a problem. The rub is that the JobID is used in several other tables for customers, jobitems, invoices, and invoices items with probably more tables in the future, and it is combined with another column in those tables to form a compound PK. This is a big opinion, but splitting the JobID up would add unneeded complexity in those other tables that have many triggers working with the JobID to automate the data entry.
Also, using the Auto Increment means we have to have a way to reset it daily as Auto Increment does not reset without being told to, and even at that, it doesn't forget where it left off without coercing . We were auto increment another "daily_increment" table and getting the last three from that, then truncating the table daily, but that was relying on a scheduled event for truncating that wasn't proving reliable during times of shut downs and unexpected power outages. Fixable stuff, but I thought eliminating the scheduled event all together would prove to be more robust.
@user1707562: No, the entire gist of my answer above is that MySQL will automatically rotate the auto-incrementation of seq on a daily basis. Normalisation as above can still be implemented without breaking FKs (you just have compound FKs), so I'm not sure what the "complexity" would be in this case.
My apologies. I missed that capability you were calling out but found it in the link to the MySQLs documention. I like your thinking. You mentioned a view, I think that might be the ticket to keeping the JobID together (we really need it to stay together, I know its hard to believe) and finding a solution. Will a view have a performance problem as the database grows?
0

So, I found a query that works (thus far).

Declare maxjobID VARCHAR(16);
Declare jobincrement SMALLINT;
SET maxjobID =
(Select MAX(
    ifnull(SUBSTRING(
        (Select JobID FROM jobs WHERE SUBSTRING(JobID,5,8)=date_format(curdate(), '%Y%m%d')),
            5,
            12),0)
        )
    );

if maxjobID=0
then set jobincrement=1;
else set jobincrement=(select substring(maxjobID,10,3))+1;
end if;

Set NEW.JobID=concat
(New.AssignedCompany,'-',date_format(curdate(), '%Y%m%d'),'-',(select lpad(jobincrement,3,'0')));

Thanks for the responses! Especially eggyal for pointing out the auto_increment capabilities in MyISAM.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.