InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:18:51 GMT20http://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx[font="Verdana"]Yes Jeff, I have noted that. Any ways Thanks :)Mahesh[/font]Wed, 11 Mar 2009 00:10:27 GMTMahesh Botehttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxLike 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.Wed, 11 Mar 2009 00:00:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx[font="Verdana"]Try this.[code]Create Table dbo.SeqNum(AccountNbr Int,CreateDate DateTime)GoInsert Into dbo.SeqNumSelect 59961, '01/05/2009' Union AllSelect 59961, '01/06/2009' Union AllSelect 59961, '01/07/2009' Union AllSelect 32187, '01/05/2009' Union AllSelect 32187, '01/06/2009' Union AllSelect 22195, '01/10/2009' Union AllSelect 22195, '01/12/2009' Union AllSelect 22195, '01/13/2009' Union AllSelect 22195, '01/15/2009' Union AllSelect 69248, '01/11/2009' Union AllSelect 69248, '01/12/2009'GoSelect ( Select Count(AccountNbr) As Seq From dbo.SeqNum B Where A.CreateDate >= B.CreateDate And A.AccountNbr = B.AccountNbr )As SeqNo , A.AccountNbr , A.CreateDateFrom dbo.SeqNum AGoDrop Table dbo.SeqNumGo[/code]Mahesh[/font]Tue, 10 Mar 2009 22:48:46 GMTMahesh Botehttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxOk... first of all, take a look at the following link to see why a thing called Triangular Joins are bad... real bad...[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]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.[code]--===== 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 #SeqWorkDECLARE @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[/code]Tue, 10 Mar 2009 22:33:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxGood enough... gimme a few minutes to hammer it out...Tue, 10 Mar 2009 21:42:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxI 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.Tue, 10 Mar 2009 21:34:16 GMTLoveSQLhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxOh 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?Tue, 10 Mar 2009 21:22:27 GMTLoveSQLhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx[quote][b]LoveSQL (3/10/2009)[/b][hr]Well the back end is SQL 2000 not SQL 2005thanks![/quote]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.Tue, 10 Mar 2009 21:20:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx[quote][b]LoveSQL (3/10/2009)[/b][hr]Well the back end is SQL 2000 not SQL 2005thanks![/quote]So why did you post your question in the SQL Server 2005 forum?Tue, 10 Mar 2009 21:15:57 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxWell the back end is SQL 2000 not SQL 2005thanks!Tue, 10 Mar 2009 21:00:33 GMTLoveSQLhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspx[quote][b]LoveSQL (3/10/2009)[/b][hr]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_NUMBERAccountNbr Seq CreateDate59961 1 01/05/0959961 2 01/06/0959961 3 01/07/0932187 1 01/05/0932187 2 01/06/0922195 1 01/10/0922195 2 01/12/0922195 3 01/13/0922195 4 01/15/0969248 1 01/11/0969248 2 01/12/09[/quote]Yep... but ya gotta tell me, why does the requirement to NOT use Row_Number or Rank exist?Tue, 10 Mar 2009 20:24:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic672929-145-1.aspxHi, 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_NUMBERAccountNbr Seq CreateDate59961 1 01/05/0959961 2 01/06/0959961 3 01/07/0932187 1 01/05/0932187 2 01/06/0922195 1 01/10/0922195 2 01/12/0922195 3 01/13/0922195 4 01/15/0969248 1 01/11/0969248 2 01/12/09Tue, 10 Mar 2009 19:36:06 GMTLoveSQL