Post reply

Using CASE Condition for Prefixing a field from a table

  • September 30, 2013 at 7:38 am

    #277600

    Hi All,

    I want to retain the same Employee Code when an employee is rehired and prefix with A,B,C based on no of times the employee has been rehired with CASE statement

    CASE WHEN LEFT(EMPLOYEECODE,1) = 'Z' THEN 'A'+EMPLOYEECODE ELSE CASE WHEN ISNUMERIC(LEFT(EMPLOYEECODE,1)) = 1 THEN 'A'+EMPLOYEECODE ELSE CHAR(ASCII(LEFT(EMPLOYEECODE,1))+1)+SUBSTRING(EMPLOYEECODE,2,99) END END

    and it is working fine with these parameters : Employee Code is 'A10010' then its returning B10010 and when it is 10010 it is returning A10010 which is correct but the challenge comes when the employee code is Z10010 then it should return AA10010 not AZ10010 ....how can i do that?? help

  • September 30, 2013 at 8:14 am

    #1654569

    This will depend on the number of levels you want to go with this, but this should accomplish what you wanted:

    CASE

    when PATINDEX('%Z%',EmployeeCode) > 0 then REPLICATE('A',PATINDEX('%[0-9]%',EmployeeCode)) + SUBSTRING(EMPLOYEECODE,PATINDEX('%[0-9]%',EmployeeCode),99)

    WHEN ISNUMERIC(LEFT(EMPLOYEECODE,1)) = 1 THEN 'A'+EMPLOYEECODE

    ELSE CHAR(ASCII(LEFT(EMPLOYEECODE,1))+1)+SUBSTRING(EMPLOYEECODE,2,99)

    END


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 30, 2013 at 8:34 am

    #1654579

    Another way to do it is to create a RehireCode table with (1,A), (2,B), ... , (27,AA), (28, AB), ... and join to that.

    27 is an awful lot of times for an employee to be hired by the same company!

    John

  • October 1, 2013 at 3:54 am

    #1654823

    thanx keith that was a help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply