NEW SEQUENCE NUMBER DAILY?
-
I need to reset a sequence number every 0000hrs daily...
I have searched and searched and tested different ways, and have found no viable solution.
Anyone know how to do this?
-
you can have trigger which will be fired as per requirement and will do the activity as scripted.
----------
Ashish -
I am a complete n00b, could you explain how to do this?
please!!!!
-
I need to reset a sequence number every 0000hrs daily...
can you explain where exactly you have to reset the sequence?
Is this a table column or something else. Please provide the detail description of your requirement
----------
Ashish -
DMS11X (3/28/2011)
I need to reset a sequence number every 0000hrs daily...I have searched and searched and tested different ways, and have found no viable solution.
Anyone know how to do this?
Can you give us a little more information on what you need to do? I'm going to guess that the sequence number is stored in a table column?
-Ki
-
Here's an article link on how you can post your code (and questions) so we have enough information to assist you.
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Your question doesn't sound very complex, but it would help us if you could post more details on where this sequence number comes from, what it does, and what reset options you've tried implementing that haven't helped you.
-
I'm going to assume that you don't mean the new sequence number feature found in SQL Denali, since that hasn't been released yet.
That means you probably mean an Identity column in a table.
When you say you're going to reset it, do you mean empty out the table and have it start over at 1, or do you want to keep the prior data and start the numbering at 1 again?
If the first (empty the table), you can reset a table and it's Identity column by using Truncate Table.
If the second (continue the data but restart the numbering), have you considered doing so at runtime instead of actually in the table, either in the front end application or by using the Row_Number() function?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
also, along the lines of what Gus said about the row_number() function, I would leave the identity key in a table, and create a view, featuring the row_number() function in it, so you cna get your daily sequence numbers, but leave the schema of the table along normal lines so you don't need a trigger to fiddle with the seuqnce you are looking for.
CREATE TABLE [dbo].[EXAMPLE] (
[EXAMPLEID] INT IDENTITY(1,1) NOT NULL,
[CREATEDDATE] DATETIME NULL DEFAULT (getdate()),
[DESCRIP] VARCHAR(30) NULL,
CONSTRAINT [PK__Example__C523A4376FE99F9F] PRIMARY KEY CLUSTERED ([EXAMPLEID]))
GO
CREATE VIEW VW_EXAMPLE
AS
SELECT
row_number() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd,0,CREATEDDATE), 0) ORDER BY [CREATEDDATE],[EXAMPLEID]) AS TheSequence,
[EXAMPLE].*
FROM [EXAMPLE]
you could even update the insert/update the view directly, as long as the DML command doesn't reference [TheSequence]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
DMS11X (3/28/2011)
I need to reset a sequence number every 0000hrs daily...I have searched and searched and tested different ways, and have found no viable solution.
Anyone know how to do this?
There are many methods of having a "sequence number" and we simply don't know which method you're using. We need more information. Are you using a "Sequence Table", and IDENTITY, a SELECT MAX, or what? We're just guessing without such information and could do more harm than good without knowing.
As a side bar, a lot of folks that do have experience in SQL Server have difficulty maintaining Sequence Numbers. It's not a task that I would have assigned a newbie to accomplish alone. I would have assigned someone who knew how to do it with the newbie in tow so (s)he could learn it without being pushed into the fire.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is what I have so far:
My table currently references the Id column, which is ok,
but I need the number that follows the date to re-cycle every 24hrs back to 1
USE [SCADD]GO
/****** Object: Table [dbo].[CALLS] Script Date: 03/28/2011 06:31:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CALLS](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DaDate] [date] NULL,
[PkDisplay] AS ((('CL-'+replace(CONVERT([varchar](10),[DaDate],(101)),'/',''))+'-')+CONVERT([varchar](20),[Id],(0))) PERSISTED,
[CALL_TYPE] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CALLS] ADD CONSTRAINT [DF__DaTable__DaDate__5EBF139D] DEFAULT (getdate()) FOR [DaDate]
GO
RESULT:
-
That's actually very simple.
Set up a SQL Agent job scheduled to run at midnight. The job step needs a single line of T-SQL
DBCC CHECKIDENT (CALLS, RESEED, 0)Needless to say, this is an odd thing to do with an identity, but....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (3/28/2011)
That's actually very simple.Set up a SQL Agent job scheduled to run at midnight. The job step needs a single line of T-SQL
DBCC CHECKIDENT (CALLS, RESEED, 0)Needless to say, this is an odd thing to do with an identity, but....
We're assuming ID is the column that needs resetting. But given the structure of the rest of the table, that's quite likely.EDIT: I just caught this line: "but I need the number that follows the date to re-cycle every 24hrs back to 1"
You're talking about the PKDisplay column, correct?
Don't persist the column. This sequencing should be done with the INSERT code. Or use of another table.
-
Brandie Tarvin (3/28/2011)
We're assuming ID is the column that needs resetting. But given the structure of the rest of the table, that's quite likely.It is, look at the OP's revised latest post. He wants that calculated column to have the number following the date reset to 1, that is based off the identity column. Hence reseed the identity to 0 and it will do what is needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hmm. Point taken. I can see that working.
-
What would I do without the internet?
You guys area great! 😎
So... What the hEcK do I do? LOL :hehe:
Please remember I am very green to SQL, I am learning every day.
Thank you guys!:Wow:
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply