Generate New Number Everyday - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Generate New Number Everyday

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-24 : 22:52:31

hi

I have a table and i would like to generate new number on each day. For Example:

ID(int) Dates(date)
1 25/09/2009
2 25/09/2009
3 25/09/2009
4 25/09/2009
1 26/09/2009
2 26/09/2009
3 26/09/2009
1 27/09/2009
2 27/09/2009
3 27/09/2009
4 27/09/2009
5 27/09/2009
6 27/09/2009

How can i accomplish this? Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-25 : 01:05:23

select row_number()over(partition by date order by date) as rid, * from tablename

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 02:55:54

If dates have time part

select row_number()over(partition by dateadd(day,datediff(day,0,date),0) order by date) as rid, * from tablename

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-25 : 03:14:50

hi

My apology if my post is not clear.

I want auto increase a number in a table but i want these number to reset to 1 if it is a new day.

How do i go about it? Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 03:54:10

quote:
Originally posted by sg2255551

hi

My apology if my post is not clear.

I want auto increase a number in a table but i want these number to reset to 1 if it is a new day.

How do i go about it? Thanks a lot

Have you tried the solution?

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-25 : 05:06:12

hi

I have tried the solution and this the right one, but how do I use it in an insert? Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 05:26:29

quote:
Originally posted by sg2255551

hi

I have tried the solution and this the right one, but how do I use it in an insert? Thanks

Did you mean updating the table?
Just use it in SELECT statement, otherwsie you need to update every day data are added to the table

Madhivanan

Failing to plan is Planning to fail

Kabila
Starting Member

33 Posts

Posted - 2009-09-25 : 05:26:46

use a Trigger to update the ID

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-25 : 06:26:32

hi

no, i don't meean updating.

ok, there is this identity seed 1, increment 1 which is used for auto increment of number by 1 starting from 1 in an int field. But it will only gives me 1,2,3,4,5,6,7 and so on. Is there a way that i can reset the number to 1 on the start of a new business day. For example, I went to service my PC today and my queue number is 3 on my queue ticket, there are 2 customers in front of me. The one behind me has a queue ticket number 4 and so on. Th next day, the queue ticket number should start from 1. Is there a way i can do this in sql? Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-09-25 : 09:58:59

hi

I have found the solution. Thanks

declare @D Nvarchar(12),@int Int
select @D = Convert(Nvarchar(12),isnull(Max(Queue_Date),''),103) from TableA
if isnull(@D,'') = Convert(Nvarchar(12),GETDATE(),103)
begin
select @int = row_number()over(partition by dateadd(day,datediff(day,0,Queue_Date),0) order by Queue_Date) from TableA where @D = Convert(Nvarchar(12),GETDATE(),103)
set @int = isNULL(@int,'') + 1
Insert into TableA(Queue_Number,Queue_Date) values(@int,GETDATE())
end
else if @D <> Convert(Nvarchar(12),GETDATE(),103)
begin
set @int = isNULL(@int,'') + 1
Insert into TableA(Queue_Number,Queue_Date) values(@int,GETDATE())
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources