Collaboration-2-Go


Lösungen und Software-Entwicklung im Collaboration Umfeld, insbesondere Microsoft SharePoint, Microsoft Exchange und Microsoft Office.

Support


Mobil: +49 (152) 53 97 78 79
Mail: service@collaboration-2-go.de
Weitere Kontaktmöglichkeiten: Kontaktseite
Dekoration: Köln



SQL Server - Sequential IDs for each month
Created: 30.11.2012
Categories: SQL Server

      Sequential IDs for each month and each month begins with an ID=1
      
      Sample Table:
      
      CREATE TABLE [dbo].[Orders](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [OrderDate] [date] NOT NULL,
      [CustomID] [int] NOT NULL,
      [Title] [nchar](50) NOT NULL
      ) ON [PRIMARY]
      


      Insert:
      
      BEGIN TRAN
        declare @currentMaxID int
        declare @newID int

        select @currentMaxID = max(CustomID)
        from [dbo].[Orders] WITH (UPDLOCK)
        where year(OrderDate) = year(getdate())
        and month(OrderDate) = month(getdate())

        if @currentMaxID is null
        begin
          set @currentMaxID = 0
        end
        set @newID = @currentMaxID + 1
        
        insert [dbo].[Orders]
        (OrderDate, CustomID, Title)
        values
        (getdate(), @newID, 'my title')
      COMMIT
      

      
      Recommendation: use the insert code within a stored procedure.
    

Post a Comment!