SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


custom sequence generator


custom sequence generator

Author
Message
LoveSQL
LoveSQL
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 154
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

AccountNbr Seq CreateDate
59961 1 01/05/09
59961 2 01/06/09
59961 3 01/07/09
32187 1 01/05/09
32187 2 01/06/09
22195 1 01/10/09
22195 2 01/12/09
22195 3 01/13/09
22195 4 01/15/09
69248 1 01/11/09
69248 2 01/12/09


rb
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85757 Visits: 41091
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

AccountNbr Seq CreateDate
59961 1 01/05/09
59961 2 01/06/09
59961 3 01/07/09
32187 1 01/05/09
32187 2 01/06/09
22195 1 01/10/09
22195 2 01/12/09
22195 3 01/13/09
22195 4 01/15/09
69248 1 01/11/09
69248 2 01/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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
LoveSQL
LoveSQL
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 154
Well the back end is SQL 2000 not SQL 2005
thanks!


rb
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5724 Visits: 11771
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85757 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
LoveSQL
LoveSQL
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 154
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
LoveSQL
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 154
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85757 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85757 Visits: 41091
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. Wink 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mahesh Bote
Mahesh Bote
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1478 Visits: 1274
Try this.


Create Table dbo.SeqNum
(
AccountNbr Int
,CreateDate DateTime
)
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 (
Select Count(AccountNbr) As Seq
From dbo.SeqNum B
Where A.CreateDate >= B.CreateDate
And A.AccountNbr = B.AccountNbr
)As SeqNo
, A.AccountNbr
, A.CreateDate
From dbo.SeqNum A
Go

Drop Table dbo.SeqNum
Go



Mahesh


MH-09-AM-8694
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search