SQL to generate new sequential identifiers when current month changes?

ALTER PROCEDURE [dbo].[generateTrackNo] AS
   DECLARE @tempYear VARCHAR(5),@tempMonth VARCHAR(5)

   SET @tempYear = Year(GetDate())
   SET @tempMonth = Month(GetDate())

   SELECT 'CAB' + SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 3, 4) + 
          RIGHT('0000000'+ CAST(CurrentNo AS VARCHAR(10)), 5) 
   FROM tblTrackNo 
   WHERE GenYear = @tempYear

   UPDATE tblTrackNo 
   SET CurrentNo = CurrentNo + 1 
   WHERE GenYear = @tempYear

My problem here is I don't know how to reset a tracking numbe开发者_运维技巧r to 001 everytime I change the MONTH.. in my sql code its working when the year change...

Example ..this is my tracking number.. CAB1108031 11 is for year 08 is for month.. when I change the month the output that I need is CAB1109001 if the month choose for the first time if not the tracking number 031 will stay and add up to the end.. thanks.

You need to change both your SELECT and UPDATE queries to include the month. You might also need to add a column for storing the month to the table tblTrackNo if there isn't already one in that table.

Example (T-)SQL:

SELECT  'CAB' + SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 3, 4) + 
        RIGHT('0000000'+ CAST(CurrentNo AS VARCHAR(10)), 5) 
FROM tblTrackNo 
WHERE   GenYear = @tempYear
        AND GenMonth = @tempMonth

UPDATE tblTrackNo 
SET CurrentNo = CurrentNo + 1 
WHERE   GenYear = @tempYear
        AND GenMonth = @tempMonth

As-is, you're effectively generating tracking numbers based only on the current year.

You will probably also need to add data to the table tblTrackNo, to at least initialize the tracking numbers for each year and number. Or you could use (T-)SQL like the following:

SELECT  'CAB' + SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 3, 4) + 
        RIGHT('0000000'+ CAST(ISNULL(CurrentNo, 1) AS VARCHAR(10)), 5) 
FROM tblTrackNo
WHERE   GenYear = @tempYear
        AND GenMonth = @tempMonth

IF EXISTS (
    SELECT *
    FROM tblTrackNo
    WHERE   GenYear = @tempYear
            AND GenMonth = @tempMonth )

    UPDATE tblTrackNo 
    SET CurrentNo = CurrentNo + 1 
    WHERE   GenYear = @tempYear
            AND GenMonth = @tempMonth

ELSE

    INSERT tblTrackNo ( GenYear, GenMonth, CurrentNo )
    VALUES ( @tempYear, @tempMonth, 2 );