[SOLVED] Incrementing ID puzzle

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

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Incrementing ID puzzle

Author

Topic

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-08 : 14:31:45

I have a DB one of our departments is using. (Access 2003)

What they have been doing is at the end of each contract, is to recreate the primary table and archive the old data so that the ID feild counter starts over.

I am thinking of moving this to SQL2005, but the incrementing field is bothering me.

The scenerio is that the contract date is from Dec 1 - Nov 30.

so rows added on dec 1, 2010 would start with ID 1 increasing by 1 until Nov 30 starting over Dec 1 again.

Bogus Table to show what I am trying to do.

PK ID CSR Date
1 1 John 12/01/2009
2 2 Don 01/01/2010
3 3 John 05/21/2010
4 4 Don 11/22/2010
5 1 John 12/05/2011
6 2 Don 05/11/2011


Declare @Test Table 
(
        PK Int,
        ID VarChar(4),
        SalesName VarChar(12),
        DateEntered DateTime
)
Insert Into @Test Values(1,'1','Mike','01/12/2009')
Insert Into @Test Values(2,'2','John','02/13/2009')
Insert Into @Test Values(3,'1','John','01/12/2010')
Insert Into @Test Values(4,'2','John','02/12/2010')
Insert Into @Test Values(5,'3','Mike','03/12/2010')
Insert Into @Test Values(6,'4','John','04/12/2010')
;

    Select Cast(Max(dbo.fnGetNumbers(ID))+1 As VarChar(1)) As ID 
    from @Test 
    where DateEntered Between DATEADD(yy, DATEDIFF(yy,0,getdate()), -365) -- 1st Prior Last Year
            and dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0)) -- Last Day Prior Year

Declare @Next Int
Insert Into @Test
    Select 
        7,
            (Select Cast(Max(dbo.fnGetNumbers(ID))+1 As VarChar(1)) 
                from @Test 
                where DateEntered >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) As ID,
        'John', '5/11/2010'
    from @Test

Select * from @Test

This is close but this is the result set.

PK          ID   SalesName    DateEntered
----------- ---- ------------ -----------------------
1           1    Mike         2009-01-12 00:00:00.000
2           2    John         2009-02-13 00:00:00.000
3           1    John         2010-01-12 00:00:00.000
4           2    John         2010-02-12 00:00:00.000
5           3    Mike         2010-03-12 00:00:00.000
6           4    John         2010-04-12 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000
7           5    John         2010-05-11 00:00:00.000

as you can see from the result set, it is inserting the entry 6 times.

Thank You,

John Fuhrman
http://www.titangs.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 16:34:21

Try just
Insert Into @Test
Select
7,
(Select Cast(Max(dbo.fnGetNumbers(ID))+1 As VarChar(1))
from @Test
where DateEntered >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) As ID,
'John', '5/11/2010'

i.e. drop the from #test

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-08 : 18:03:04

OK, Nigel, why was it that easy??

Thank You,

John Fuhrman
http://www.titangs.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources