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


Mobil: +49 (152) 53 97 78 79
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]

        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
          set @currentMaxID = 0
        set @newID = @currentMaxID + 1
        insert [dbo].[Orders]
        (OrderDate, CustomID, Title)
        (getdate(), @newID, 'my title')

      Recommendation: use the insert code within a stored procedure.

Send us a Comment!