Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

increment values on day basis on column Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:42 PM
Points: 11, Visits: 159
hi all,

i want to generate id column values on the basis of every day.

suppose table name like test and
table have column id then value of id column are

today is 28-aug-2012 then values are increased after each insert like this
:-
table name test

column name ID
and values are

id
2012082800001
2012082800002
....
....
2012082800002
and next day it will be on new insert :-
2012082900001
2012082900002
.......
........
and next day it will be on new insert :-
2012083000001
2012083000002
.......
........
Post #1350715
Posted Tuesday, August 28, 2012 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,864, Visits: 14,165
Generating your own identity values isn't as straightforward as it might at first appear, the code for preventing concurrency issues is fairly complex. Why don't you use the identity column supported by SQL Server and generate this pseudokey on the fly?

;WITH SampleData AS (
SELECT ID, InsertDateDT = CAST(InsertDate AS DATETIME)
FROM (VALUES (1,'20120828'), (2,'20120828'), (3,'20120829'),(4,'20120829')) d (ID, InsertDate)
)
SELECT
ID,
InsertDateDT,
MyBigintCompositeID = CAST(
CONVERT(VARCHAR(8),InsertDateDT,112)
+ RIGHT('00000'+CAST(ROW_NUMBER() OVER(PARTITION BY InsertDateDT ORDER BY ID) AS VARCHAR(5)),5)
AS BIGINT)
FROM SampleData



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1350759
Posted Tuesday, August 28, 2012 3:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 318, Visits: 1,084
This will work for adding one at a time:


--------------------------------------------------------------
drop table #test;
create table #test ( id varchar(13) );
--------------------------------------------------------------
declare @id varchar(13), @nextid varchar(13);

select @id = max(id) from #test;

select @nextid = case when left(@id, 8) = convert(varchar(8), getdate(), 112)
then convert(varchar(8), getdate(), 112) +
right('0000' + cast(cast(substring(@id, 9, 5) as int) +1 as varchar(5)),5)
else convert(varchar(8), getdate(), 112) + '00001'
end;

insert into #test values ( @nextid );
--------------------------------------------------------------
select * from #test;
--------------------------------------------------------------


Post #1350764
Posted Tuesday, August 28, 2012 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:42 PM
Points: 11, Visits: 159
HI ALL

PLEASE PROVIDE SOME EASY WAY IF YOU KNOW

THANKS
Post #1351052
Posted Tuesday, August 28, 2012 9:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 2,873, Visits: 5,189
anuj12paritosh (8/28/2012)
HI ALL

PLEASE PROVIDE SOME EASY WAY IF YOU KNOW

THANKS


The easiest way to achieve that will be switching to SQL2012 and using sequences.

All others ways will not be easy at all and will always have some issue with concurrency.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1351061
Posted Tuesday, August 28, 2012 9:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:59 PM
Points: 3,919, Visits: 8,898
Eugene Elutin (8/28/2012)
anuj12paritosh (8/28/2012)
HI ALL

PLEASE PROVIDE SOME EASY WAY IF YOU KNOW

THANKS


The easiest way to achieve that will be switching to SQL2012 and using sequences.

All others ways will not be easy at all and will always have some issue with concurrency.


+1



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351066
Posted Tuesday, August 28, 2012 7:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 3,422, Visits: 5,368
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:

CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)

INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END

INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

SELECT * FROM #t

DROP TABLE #t





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1351363
Posted Tuesday, August 28, 2012 8:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 35,584, Visits: 32,173
laurie-789651 (8/28/2012)
This will work for adding one at a time:


--------------------------------------------------------------
drop table #test;
create table #test ( id varchar(13) );
--------------------------------------------------------------
declare @id varchar(13), @nextid varchar(13);

select @id = max(id) from #test;

select @nextid = case when left(@id, 8) = convert(varchar(8), getdate(), 112)
then convert(varchar(8), getdate(), 112) +
right('0000' + cast(cast(substring(@id, 9, 5) as int) +1 as varchar(5)),5)
else convert(varchar(8), getdate(), 112) + '00001'
end;

insert into #test values ( @nextid );
--------------------------------------------------------------
select * from #test;
--------------------------------------------------------------




Ohhhh.... be careful Laurie. A whole lot can happen between the time you get the max id and the time you use it. On high usage systems (or "unlucky" times on low usage systems) you'll end up with duplicate ID's (and dupe errors if the column is unique) with that code. The "obvious fix' of using an explicit transaction will lead to hundreds of deadlocks per day.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1351371
Posted Tuesday, August 28, 2012 9:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 35,584, Visits: 32,173
dwain.c (8/28/2012)
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:

CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)

INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END

INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

SELECT * FROM #t

DROP TABLE #t




That's seriously clever especially since it solves the multi-row insert problem that most people experience with custom daily dequences but what happens if the number of inserts exceeds 99,999 rows in a day? It won't even warn you that it did a wrap around. And, as fast as it is, there's still the possibility of someone getting in between the DECLARE and the DBCC.

What about inserts that last from slightly before midnight to slightly after midnight? If you want them to accurately reflect the date they were inserted into the table, you'll be disappointed. Of course, the same would be true of you had a default of GETDATE() on the table.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1351374
Posted Tuesday, August 28, 2012 9:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 35,584, Visits: 32,173
anuj12paritosh (8/28/2012)
hi all,

i want to generate id column values on the basis of every day.

suppose table name like test and
table have column id then value of id column are
AA
today is 28-aug-2012 then values are increased after each insert like this
:-
table name test

column name ID
and values are

id
2012082800001
2012082800002
....
....
2012082800002
and next day it will be on new insert :-
2012082900001
2012082900002
.......
........
and next day it will be on new insert :-
2012083000001
2012083000002
.......
........


I REALLY hope I can talk you and the people you work for out of this idea. At the root of the problem, it's a violation of normal form because you have a column that contains two distinct values... the date of insertion and a very dependent daily sequence number. You also have a growth problem. Yeah... I know you'll say that you'll never go over 99,999 rows in a day but a lot of consultants make a lot of money off of people who thought the same thing.

And, let me ask... what do you want to do if someone deletes a row?

I implore you not to use this column for anything except display purposes which also means don't store it in your database. Calculate it at display time like ChrisM did with the understanding that if someone does a delete, the sequence numbers will change.

If I can't talk you out of it, then wait until the end of each day and calculate the column once for the previous day using code like ChrisM used. Dwain's code is pretty decent and there's a low probability of someone getting in out of sequence but that's not quite the same as saying that it's guaranteed to never dupe a row during the change at midnight.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1351376
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse