t-sql autoincrement with month
Hi Experts,
What i need to is when every time month start from 01 for example then reset to end of the month to 01 again see example YYMM[Five digit]
"200700001"
"200700002"
"200700003"
.
.
"200800001"
"200800002"
.
.
"200900001"
"200900002"
"200900003"
.
.
"200900500"
Code i have tried it's not working properly
[code]
--Created Table for dates
CREATE TABLE [tblCalendar]
(ID BIGINT NOT NULL IDENTITY(1,1) ,
[CalendarDate] DATETIME
)
---dROP TABLE tblCalendar
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO [tblCalendar]
(
CalendarDate
)
SELECT
@StartDate
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
--==============================================
DECLARE @NewLableNumber NVARCHAR(1300)
DECLARE @NewTempNumber NVARCHAR(1300)
DECLARE @CurrentDatetime Datetime
DECLARE @CurrentGenerateNumberID INT
SET @CurrentDatetime=GETDATE()
;WITH CTERecords (GenerateNumber,YYMM)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(CONVERT(VARCHAR(10),CalendarDate,112),6) ORDER BY ID) AS GenerateNumber,LEFT(CONVERT(VARCHAR(10),CalendarDate,112),6) AS YYMM
FROM tblCalendar WHERE LEFT(CONVERT(VARCHAR(10),CalendarDate,112),6)=LEFT(CONVERT(VARCHAR(10),@CurrentDatetime,112),6)
)
SELECT @CurrentGenerateNumberID=MAX( GenerateNumber)
FROM CTERecords
SELECT @CurrentGenerateNumberID+1
[/Code]
Any idea much appriciated
What i need to is when every time month start from 01 for example then reset to end of the month to 01 again see example YYMM[Five digit]
"200700001"
"200700002"
"200700003"
.
.
"200800001"
"200800002"
.
.
"200900001"
"200900002"
"200900003"
.
.
"200900500"
Code i have tried it's not working properly
[code]
--Created Table for dates
CREATE TABLE [tblCalendar]
(ID BIGINT NOT NULL IDENTITY(1,1) ,
[CalendarDate] DATETIME
)
---dROP TABLE tblCalendar
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO [tblCalendar]
(
CalendarDate
)
SELECT
@StartDate
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
--==============================================
DECLARE @NewLableNumber NVARCHAR(1300)
DECLARE @NewTempNumber NVARCHAR(1300)
DECLARE @CurrentDatetime Datetime
DECLARE @CurrentGenerateNumberID INT
SET @CurrentDatetime=GETDATE()
;WITH CTERecords (GenerateNumber,YYMM)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(CONVERT(VARCHAR(10),CalendarDate,112),6) ORDER BY ID) AS GenerateNumber,LEFT(CONVERT(VARCHAR(10),CalendarDate,112),6) AS YYMM
FROM tblCalendar WHERE LEFT(CONVERT(VARCHAR(10),CalendarDate,112),6)=LEFT(CONVERT(VARCHAR(10),@CurrentDatetime,112),6)
)
SELECT @CurrentGenerateNumberID=MAX( GenerateNumber)
FROM CTERecords
SELECT @CurrentGenerateNumberID+1
[/Code]
Any idea much appriciated
It is not clear how you want to use this incremental values. Do you want to generate it when you insert a new row into a specific table?
ASKER
@Máté Farkas :Yes
ASKER
@ste5an :
if you like this it will create always 1, but if i tried to use ROW_NUMBER() OVER(PARTITION BY LEFT(CONVERT(VARCHAR(10),TestDate,112),6) ORDER BY ID)
it will create 1,2,3 and it will change the next month as 1 .I think i'm very close to answer.but still not working :(
one logic is missing
ROW_NUMBER() OVER ( PARTITION BY EOMONTH(DateColumn) ORDER BY DateColumn)
if you like this it will create always 1, but if i tried to use ROW_NUMBER() OVER(PARTITION BY LEFT(CONVERT(VARCHAR(10),TestDate,112),6) ORDER BY ID)
it will create 1,2,3 and it will change the next month as 1 .I think i'm very close to answer.but still not working :(
one logic is missing
ROW_NUMBER() OVER ( PARTITION BY EOMONTH(DateColumn) ORDER BY DateColumn)
ASKER
Hi , I have wrote following way, it's looks working fine.But if you can give me feedback much appreciated.
Any issues please kindly let me know
Any issues please kindly let me know
;WITH CTERecords (GenerateNumber)
AS
(SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(CONVERT(VARCHAR(10),TestDate,112),6) ORDER BY ID) AS GenerateNumber
FROM tblTest
WHERE LEFT(CONVERT(VARCHAR(10),TestDate,112),6)=LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6)
)
SELECT @CurrentGenerateNumberID=MAX(GenerateNumber)
FROM CTERecords
SET @CurrentGenerateNumberID= @CurrentGenerateNumberID+1
SET @NewLableNumber=FORMAT(@CurrentGenerateNumberID,'00000')
I think now I understand what you want.
Let's say the target table TestData where you want to insert new records is the following:
You have a SourceData table or view or query which is the source of the inserted rows. This source table has a TestDate field.
Then you want to insert rows from SourceTable to TestData:
Let's say the target table TestData where you want to insert new records is the following:
CREATE TABLE TestData(GeneratedNumber int, ...)You have a SourceData table or view or query which is the source of the inserted rows. This source table has a TestDate field.
CREATE TABLE SourceData(TestDate datetime, ...)Then you want to insert rows from SourceTable to TestData:
;WITH CTERecords AS ( -- this finds the last number for each month
SELECT LEFT(GeneratedNumber, 4) AS YYMM,
CAST(MAX(RIGHT(GeneratedNumber, 5)) AS INT) AS LastNumber
FROM TestData
GROUP BY LEFT(GeneratedNumber, 4)
)
INSERT INTO TestData(GeneratedNumber)
SELECT CONVERT(varchar(4), s.TestDate, 12) + -- this generates a higher numbers for each month
RIGHT(
CONCAT('00000',
COALESCE(c.LastNumber, 0) +
(ROW_NUMBER() OVER(PARTITION BY YEAR(s.TestDate), MONTH(s.TestDate) ORDER BY s.TestDate))), 5) AS GeneratedNumber
FROM SourceData s
LEFT JOIN CTERecords c ON c.YYMM = CONVERT(varchar(4), s.TestDate, 12)
Well, without a concise and complete example, I (we) can only guess.
For SQL questions a concise and complete example consists of a single, runnable script containing the table DDL - preferably as table variables - and data INSERT statements. And an description of the desired output. Also a list of your requirements. For example, without them, there is no need to take a part from the date only. Cause the results are arbitrary within the month.
For SQL questions a concise and complete example consists of a single, runnable script containing the table DDL - preferably as table variables - and data INSERT statements. And an description of the desired output. Also a list of your requirements. For example, without them, there is no need to take a part from the date only. Cause the results are arbitrary within the month.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What's wrong with ROW_NUMBER() OVER ( PARTITION BY EOMONTH(DateColumn) ORDER BY DateColumn)?