CODEWITHSUNDEEP
mysql
Linga
Linga

Reputation: 10563

MYSQL Query to find the value

I've a table called PO_JOBS which contains a column called PO_NUMBER. The value should be in the format of PO_2014_JAN_1 (Prefix with PO_ , current year, three letters of month, and the last value is incremented).

For example,

The PO_Numbers for every month is 

PO_2014_JAN_1
PO_2014_JAN_2
....
PO_2014_FEB_1 
PO_2014_FEB_2
...
PO_2015_JAN_1
....

I've tried this

SELECT
     CONCAT(('PO_'),
     YEAR(CURRENT_TIMESTAMP),
     '_',
     SUBSTRING(UPPER(MONTHNAME(CURRENT_TIMESTAMP)),1,3),
     '_',
     IF(
       LOCATE(
          CONCAT(YEAR(CURRENT_TIMESTAMP),
          '_',
          SUBSTRING(UPPER(MONTHNAME(CURRENT_TIMESTAMP)),1,3)),
       PO_NUMBER)>0,
          MAX(CAST(SUBSTRING(PO_NUMBER,13) AS UNSIGNED))+1,
       1))
 FROM PO_JOBS

But it doesn't increment the value (ie) always return 1 (PO_2014_FEB_1). I hope you understand my problem.

My goal is to generate PO_NUMBER based on PO_current year_Three letters of current month_incremented value

Upvotes: 0

Views: 59

Answers (3)

Linga
Linga

Reputation: 10563

I've solved this by the following query. Thanks to vadaica's helpful answer

SELECT 
    CONCAT(
        ('PO_'),
        YEAR(CURRENT_TIMESTAMP),
        '_',
        SUBSTRING(UPPER(MONTHNAME(CURRENT_TIMESTAMP)),1,3),
        '_',
        IFNULL(MAX(CAST(
            replace(PO_NUMBER,
            concat('PO_',
                year(current_timestamp),
                '_',
                SUBSTRING(UPPER(MONTHNAME(CURRENT_TIMESTAMP)),1,3),
                '_'),'') AS UNSIGNED ))
        +1,1)) AS number
    from po_jobs

Upvotes: 1

vidang
vidang

Reputation: 1771

Try the following query:

set @prefix := concat('PO_', year(current_timestamp),'_', SUBSTRING(UPPER(MONTHNAME(CURRENT_TIMESTAMP)),1,3), '_');

SELECT concat(@prefix, IFNULL(max(number), 0) + 1) AS next FROM
(

  SELECT CAST( replace(PO_NUMBER,@prefix,'') AS UNSIGNED ) AS number

  FROM PO_JOBS WHERE PO_NUMBER LIKE concat(@prefix,'%')

) AS numbers

Upvotes: 1

Tom Ron
Tom Ron

Reputation: 6181

It sound like an auto increment problem. I think that from efficiency reasons it would be better to add columns of insertion \ update date and another row of auto increment value.

Another option on insertion would be to extract this number based on the number of lines in the table but it I see may troubles that can come out using this option.

Upvotes: 0

Related Questions