none
using RIGHT MAX CHARINDEX to get value

    Question

  • declare @lotTech varchar(64)

    declare @lontNum varchar(64)

    SELECT @lotTech = RIGHT(MAX(l.lotID),CHARINDEX(' ','TECH')) FROM lots l if (@lotTech = '') SET @lotNum = 'TECH 1'; else SET @lotNum = 'TECH' + SPACE(1) + @lotTech + 1;

    Can't get this to find if there is a null value or max value and 1 to concatnated data? 

    • Edited by tonofit Tuesday, May 06, 2014 6:18 PM
    Tuesday, May 06, 2014 5:25 PM

Answers

  • Try:

    if (@gradeDesc = 'TECH GRADE')
    	begin	
    		SELECT @lotTech = MAX(REPLACE(l.lotID, 'TECH ',''))
    			 FROM  lots l
    WHERE LotID LIKE 'TECH [0-9]%'
    
    		if @lotTech IS NULL
    			SET @lotNum = 'TECH 1';
    		else
    			SET @lotNum = 'TECH ' + CONVERT(VARCHAR(10),CAST(@lotTech as INT) + 1);
    	end	


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by tonofit Tuesday, May 06, 2014 7:17 PM
    Tuesday, May 06, 2014 6:41 PM
    Moderator

All replies

  • declare @lotTech varchar(64)
    
    SELECT @lotTech = RIGHT(MAX(l.lotID),CHARINDEX(' ','TECH'))
    			 FROM  lots l
    		if (@lotTech = '')
    			SET @lotNum = 'TECH 1';
    		else
    			SET @lotNum = 'TECH' + SPACE(1) + @lotTech + 1;
    Can't get this to find if there is a null value or max value and 1 to concatnated data? 
    forgot to declare lotnum variable? and what is lots.. post complet DDL.

    - please mark correct answers


    Tuesday, May 06, 2014 5:28 PM
  • Can you post some rows in lots table and desired output? What exactly are you looking for - finding the last number in that lotID column following the tech word? 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 06, 2014 5:30 PM
    Moderator
  • @productID as bigint,
     @lotNum as varchar(64),
     @manufactureDate as datetime,
     @expirationDate as datetime = null,
     @gradeDesc as varchar(64)
     ) 
    as
    BEGIN
    
    	declare @lotcheck varchar(64)
    	declare @lotTech varchar(64)
    	
    	SELECT @lotcheck = lotid FROM Lots WHERE lotid = @lotNum
    
    	if (@expirationDate is null)
    	begin
    		SELECT @expirationDate = dateadd(day, ch.expiration, @manufactureDate)
    			FROM products p
    				INNER JOIN chemicals ch on p.chemicalID = ch.chemicalID
    			WHERE p.productID = @productID
    	end
    
    	if (@gradeDesc = 'TECH GRADE')
    	begin	
    			SELECT @lotTech = RIGHT(MAX(l.lotID),CHARINDEX(' ','TECH'))
    			 FROM  lots l
    		if (@lotTech = '')
    			SET @lotNum = 'TECH 1';
    		else
    			SET @lotNum = 'TECH' + SPACE(1) + @lotTech + 1;
    	end	
    	if (@lotcheck is null)
    	begin	
    	INSERT INTO Lots(lotID, chemicalID, expirationDate, manufactureDate, confirmed, productID)
    		SELECT @lotNum as lotID, p.chemicalID, @expirationDate as expirationDate, @manufactureDate as manufactureDate, 0 as confirmed, p.productID
    			FROM products p
    				INNER JOIN chemicals ch on p.chemicalID = ch.chemicalID
    			WHERE p.productID = @productID 
    	end
    	else
    	begin
    		UPDATE Lots 
    		SET manufactureDate = @manufactureDate, 
    			expirationDate = @expirationDate,
    			productID = @productID
    		WHERE lotID = @lotNum
    	end
    	return
    END

    I'm showing more of the TSQL which works fine but the part I'm not understanding is the use of essentially parsing the data that is TECH and the number which has to be the maximum number for TECH.

    The output should be an ascending value concatnated with the word 'TECH' and a space.  Initially there is nothing in the lotNum column.  I need to get the MAX number out of the lots table for the paticular grade descripton -  'TECH GRADE' and then add 1 to that retrived number for the insert of the record. 

    So the first record to be inserted after the MAX number is found to be not present is

    TECH 1

    Following queries have to find the MAX number in the whole table and then add 1  and insert when 'TECH GRADE' description so

    TECH 2

    TECH 3

    .

    .




    • Edited by tonofit Tuesday, May 06, 2014 6:38 PM
    Tuesday, May 06, 2014 6:30 PM
  • You can use a CASE expression instead of IF ELSE:

    SET @lotNum = CASE WHEN @lotTech = '' THEN 'TECH 1'
                  ELSE  CONCAT('TECH', SPACE(1), @lotTech + '1') END;

    CASE examples: http://www.sqlusa.com/bestpractices/training/scripts/casefunction/



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Tuesday, May 06, 2014 6:39 PM
    Moderator
  • Try:

    if (@gradeDesc = 'TECH GRADE')
    	begin	
    		SELECT @lotTech = MAX(REPLACE(l.lotID, 'TECH ',''))
    			 FROM  lots l
    WHERE LotID LIKE 'TECH [0-9]%'
    
    		if @lotTech IS NULL
    			SET @lotNum = 'TECH 1';
    		else
    			SET @lotNum = 'TECH ' + CONVERT(VARCHAR(10),CAST(@lotTech as INT) + 1);
    	end	


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by tonofit Tuesday, May 06, 2014 7:17 PM
    Tuesday, May 06, 2014 6:41 PM
    Moderator