Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

custom sequence generator Expand / Collapse
Author
Message
Posted Tuesday, March 10, 2009 7:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:57 PM
Points: 137, Visits: 124
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
Post #672929
Posted Tuesday, March 10, 2009 8:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #672940
Posted Tuesday, March 10, 2009 9:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:57 PM
Points: 137, Visits: 124
Well the back end is SQL 2000 not SQL 2005
thanks!



rb
Post #672957
Posted Tuesday, March 10, 2009 9:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 3,122, Visits: 11,405
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?


Post #672960
Posted Tuesday, March 10, 2009 9:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #672961
Posted Tuesday, March 10, 2009 9:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:57 PM
Points: 137, Visits: 124
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
Post #672962
Posted Tuesday, March 10, 2009 9:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:57 PM
Points: 137, Visits: 124
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
Post #672966
Posted Tuesday, March 10, 2009 9:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #672970
Posted Tuesday, March 10, 2009 10:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #672981
Posted Tuesday, March 10, 2009 10:48 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:10 AM
Points: 1,093, Visits: 1,221
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
Post #672984
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse