custom sequence generator

Question

custom sequence generator

LoveSQL

Old Hand

Points: 397
More actions
March 10, 2009 at 7:36 pm

#97308

Hi,
Can some one help me writing the following query?
Update the Seq numbers in the table below. Seq numbers must be ordered by their CreateDate and reset for each distinct AccountNbr, as seen below
BTW, without using RANK or Row_NUMBER
AccountNbrSeqCreateDate
59961101/05/09
59961201/06/09
59961301/07/09
32187101/05/09
32187201/06/09
22195101/10/09
22195201/12/09
22195301/13/09
22195401/15/09
69248101/11/09
69248201/12/09

rb

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

LoveSQL (3/10/2009)
Hi,
Can some one help me writing the following query?
Update the Seq numbers in the table below. Seq numbers must be ordered by their CreateDate and reset for each distinct AccountNbr, as seen below
BTW, without using RANK or Row_NUMBER
AccountNbrSeqCreateDate
59961101/05/09
59961201/06/09
59961301/07/09
32187101/05/09
32187201/06/09
22195101/10/09
22195201/12/09
22195301/13/09
22195401/15/09
69248101/11/09
69248201/12/09

Yep... but ya gotta tell me, why does the requirement to NOT use Row_Number or Rank exist?

--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)

LoveSQL Old Hand Points: 397 More actions · Answer 2

Well the back end is SQL 2000 not SQL 2005

thanks!

rb

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

LoveSQL (3/10/2009)
Well the back end is SQL 2000 not SQL 2005
thanks!

So why did you post your question in the SQL Server 2005 forum?

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

LoveSQL (3/10/2009)
Well the back end is SQL 2000 not SQL 2005
thanks!

Heh... understood. And I agree with Micheal, it would have been better if you posted your question in the SQL Server 2000 forum. But, accidents happen... I've done it myself.

Tell me, just to be sure... is the Seq column a physical column in the SeqNumbers table? Also, what is the clustered index on that table. This isn't difficult nor slow, but I do need to know the particulars.

--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)

LoveSQL Old Hand Points: 397 More actions · Answer 5

Oh I got your point. Sorry I was dumb not figuring out this is 2005. Any way would it prevent you posting the answer? or should I post the question in SQL 2000 forum?

rb

LoveSQL Old Hand Points: 397 More actions · Answer 6

I suppose this Seq column is a logical column. How ever I would love to see answers to both logical and physical since I am experimenting.

rb

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

Good enough... gimme a few minutes to hammer it out...

--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)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

Ok... first of all, take a look at the following link to see why a thing called Triangular Joins are bad... real bad...

http://www.sqlservercentral.com/articles/T-SQL/61539/

I'd post the link to the "running totals" article, but I'm in the process of rewriting it. So, you'll have to trust me. 😉 Read the comments in the code below. Also, see the link in my signature below for a really helpful way to post data. Thanks.

--===== Create a test table.

-- This is NOT part of the solution.

IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL

DROP TABLE #YourTable

CREATE TABLE #YourTable

(

AccountNbr INT,

CreateDate DATETIME

)

--===== Populate the test table with data.

-- This is NOT part of the solution.

INSERT INTO #YourTable

(AccountNbr,CreateDate)

SELECT '59961','01/05/09' UNION ALL

SELECT '59961','01/06/09' UNION ALL

SELECT '59961','01/07/09' UNION ALL

SELECT '32187','01/05/09' UNION ALL

SELECT '32187','01/06/09' UNION ALL

SELECT '22195','01/10/09' UNION ALL

SELECT '22195','01/12/09' UNION ALL

SELECT '22195','01/13/09' UNION ALL

SELECT '22195','01/15/09' UNION ALL

SELECT '69248','01/11/09' UNION ALL

SELECT '69248','01/12/09'

--===== You might be tempted to use this, but it contains a

-- triangular join and will slow down in a hurry if you

-- have more than just a handful of rows for each AccountNbr.

-- I posted this to warn you that it's bad.

SELECT AccountNbr,

CreateDate,

Seq = (SELECT COUNT(*)

FROM #yourtable i

WHERE i.AccountNbr = o.AccountNbr

AND i.CreateDate <= o.CreateDate)

FROM #yourtable o

ORDER BY o.AccountNbr, o.CreateDate

--===== Instead, use the following method which will solve a

-- million rows in about 7 seconds.

IF OBJECT_ID('TempDB..#SeqWork') IS NOT NULL

DROP TABLE #SeqWork

DECLARE @PrevAccountNbr INT,

-- @PrevCreateDate DATETIME,

@PrevSeq INT

SELECT ISNULL(AccountNbr,0) AS AccountNbr,

ISNULL(CreateDate,0) AS CreateDate,

CAST(0 AS INT) AS Seq

INTO #SeqWork

FROM #yourtable

ORDER BY AccountNbr, CreateDate

ALTER TABLE #SeqWork

ADD PRIMARY KEY CLUSTERED (AccountNbr, CreateDate)

UPDATE #SeqWork

SET @PrevSeq = Seq = CASE WHEN AccountNbr = @PrevAccountNbr THEN @PrevSeq+1 ELSE 1 END,

@PrevAccountNbr = AccountNbr

FROM #SeqWork WITH(INDEX(0),TABLOCKX)

SELECT *

FROM #SeqWork

ORDER BY AccountNbr, CreateDate

--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)

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 9

[font="Verdana"]Try this.

Create Table dbo.SeqNum

(

AccountNbrInt

,CreateDateDateTime

)

Go

Insert Into dbo.SeqNum

Select 59961, '01/05/2009' Union All

Select 59961, '01/06/2009' Union All

Select 59961, '01/07/2009' Union All

Select 32187, '01/05/2009' Union All

Select 32187, '01/06/2009' Union All

Select 22195, '01/10/2009' Union All

Select 22195, '01/12/2009' Union All

Select 22195, '01/13/2009' Union All

Select 22195, '01/15/2009' Union All

Select 69248, '01/11/2009' Union All

Select 69248, '01/12/2009'

Go

Select(

SelectCount(AccountNbr) As Seq

Fromdbo.SeqNum B

WhereA.CreateDate >= B.CreateDate

And A.AccountNbr = B.AccountNbr

)As SeqNo

, A.AccountNbr

, A.CreateDate

Fromdbo.SeqNum A

Go

Drop Table dbo.SeqNum

Go

Mahesh[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

Like I said in my post above, Mahesh... be real careful. One big group of AccountNbr's and that triangular join you made will crush the server.

--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)

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 11

[font="Verdana"]Yes Jeff, I have noted that. Any ways Thanks 🙂

Mahesh[/font]

MH-09-AM-8694