locked
Refno Generate Based On Category RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data

    Batch No Item Name Category
    B001/18-19 Apple Fruits
    B002/18-19 Mango Fruits
    W001/18-19 Soap1 Soap
    W002/18-19 Soap2 Soap
    B003/18-19 Banana Fruits

    Now I want select query if I pass Furits then it has to show B004/18-19

    If I pass Soap then it has to show W003/18-19

    How would be the sql query

    Tuesday, March 12, 2019 8:34 AM

Answers

  • User753101303 posted

    Hi,

    So you want the max value for a category and increase numbers found between characters 2 and 4 ? It could give something such as :

    SELECT LEFT(MAX(BatchNo),1)+FORMAT(SUBSTRING(MAX(BatchNo),2,3)+1,'000')+RIGHT(MAX(BatchNo),6)
    FROM
    (
    	VALUES('W001/18-19','Soap1','Soap'),('W002/18-19','Soap2','Soap'),('B003/18-19','Banana','Fruits')
    )
    AS SampleData(BatchNo,ItemName,Category)
    --WHERE Category='Fruits'
    WHERE Category='Soap'
    
    

    Not sure what you want to do if nothing matches a category. Concurrency could be an issue (I always try to have that as part of an INSERT or UPDATE statement and to have my own internal identity based id).

    You could also have a look at https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-2017 in case it could make sense to create a B and a W sequence.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 12, 2019 2:06 PM
  • User77042963 posted
    SELECT LEFT(MAX(BatchNo),1)+Right('000'+Cast(Cast(SUBSTRING(MAX(BatchNo),2,3) as int)+1 as varchar(10)),3)+RIGHT(MAX(BatchNo),6)
    FROM
    (
    	VALUES('W001/18-19','Soap1','Soap'),('W002/18-19','Soap2','Soap'),('B003/18-19','Banana','Fruits')
    )
    AS SampleData(BatchNo,ItemName,Category)
    --WHERE Category='Fruits'
    WHERE Category='Soap'
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 13, 2019 1:56 PM

All replies

  • User753101303 posted

    Hi,

    So you want the max value for a category and increase numbers found between characters 2 and 4 ? It could give something such as :

    SELECT LEFT(MAX(BatchNo),1)+FORMAT(SUBSTRING(MAX(BatchNo),2,3)+1,'000')+RIGHT(MAX(BatchNo),6)
    FROM
    (
    	VALUES('W001/18-19','Soap1','Soap'),('W002/18-19','Soap2','Soap'),('B003/18-19','Banana','Fruits')
    )
    AS SampleData(BatchNo,ItemName,Category)
    --WHERE Category='Fruits'
    WHERE Category='Soap'
    
    

    Not sure what you want to do if nothing matches a category. Concurrency could be an issue (I always try to have that as part of an INSERT or UPDATE statement and to have my own internal identity based id).

    You could also have a look at https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-2017 in case it could make sense to create a B and a W sequence.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 12, 2019 2:06 PM
  • User475983607 posted

    Hello

    This is my table data

    Batch No Item Name Category
    B001/18-19 Apple Fruits
    B002/18-19 Mango Fruits
    W001/18-19 Soap1 Soap
    W002/18-19 Soap2 Soap
    B003/18-19 Banana Fruits

    Now I want select query if I pass Furits then it has to show B004/18-19

    If I pass Soap then it has to show W003/18-19

    How would be the sql query

    The solution for this type of programming problem has been discussed at length in your previous threads.  The standard solution is implementing a compound key; multiple columns.  Multiple columns will simplify the logic and make for much better performance than relying on string manipulation.

    Tuesday, March 12, 2019 2:41 PM
  • User-807418713 posted

    hello

    how would be this query in sql 2008

    format not support in 2008

    ELECT LEFT(MAX(BatchNo),1)+FORMAT(SUBSTRING(MAX(BatchNo),2,3)+1,'000')+RIGHT(MAX(BatchNo),6)
    FROM
    (
    	VALUES('W001/18-19','Soap1','Soap'),('W002/18-19','Soap2','Soap'),('B003/18-19','Banana','Fruits')
    )
    AS SampleData(BatchNo,ItemName,Category)
    --WHERE Category='Fruits'
    WHERE Category='Soap'

    Tuesday, March 12, 2019 5:09 PM
  • User1724605321 posted

    Hi Gopi.MCA,

    You can try something like :

    select concat(left(max(field_name),3),right(max(field_name),3) +1) from table_name
    

    But i keep the same idea with @mgebhard , avoid storing composite information in a single column. If the prefix varies, store that in a separate column. Keep the numeric data in separate column .

    Best Regards,

    Nan Yu

    Wednesday, March 13, 2019 2:13 AM
  • User-807418713 posted

    Hello

    Can any one give me this code in sql 2008 format

    SELECT LEFT(MAX(BatchNo),1)+FORMAT(SUBSTRING(MAX(BatchNo),2,3)+1,'000')+RIGHT(MAX(BatchNo),6)
    FROM
    (
    	VALUES('W001/18-19','Soap1','Soap'),('W002/18-19','Soap2','Soap'),('B003/18-19','Banana','Fruits')
    )
    AS SampleData(BatchNo,ItemName,Category)
    --WHERE Category='Fruits'
    WHERE Category='Soap'
    Wednesday, March 13, 2019 5:13 AM
  • User77042963 posted
    SELECT LEFT(MAX(BatchNo),1)+Right('000'+Cast(Cast(SUBSTRING(MAX(BatchNo),2,3) as int)+1 as varchar(10)),3)+RIGHT(MAX(BatchNo),6)
    FROM
    (
    	VALUES('W001/18-19','Soap1','Soap'),('W002/18-19','Soap2','Soap'),('B003/18-19','Banana','Fruits')
    )
    AS SampleData(BatchNo,ItemName,Category)
    --WHERE Category='Fruits'
    WHERE Category='Soap'
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 13, 2019 1:56 PM