InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:58:35 GMT20http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]rog pike (1/17/2009)[/b][hr][quote][b]Jeff Moden (1/17/2009)[/b][hr]Simon,I wonder if you'd do me a favor... I realize it's been about 4 months since the last post, but I need to ask... why did you need to do this? I mean, what is the business reason for doing this? Can you explain please?[/quote]Here is a sample of posts that, IMHO, are all similar in nature. They all imply finding a rank (ie. a dense rank) that form groups not explicitly defined by the data. Perhaps you'll find what your looking for among them. There are actually tons of these posts but users struggle with describing them in the subject so they remain elusive as does an agreed upon solution:)T-SQL Black Belt 'Identifying Sections' By:Itzik Ben-Gan [url=http://www.sqlmag.com/Article/ArticleID/95912/sql_server_95912.html ][b]http://www.sqlmag.com/Article/ArticleID/95912/sql_server_95912.html [/b][/url]microsoft.public.sqlserver.programmingJul 5 1999'Difficult SQL Question'[url=http://tinyurl.com/367db2 ][b]http://tinyurl.com/367db2 [/b][/url] microsoft.public.sqlserver.programmingJun 11 2001'SQL challenge: coelesce consecutive and overlapping bookings for a room'[url=http://tinyurl.com/38ebhl ][b]http://tinyurl.com/38ebhl [/b][/url] T-SQL (SS2K5)'How to get start and end value from a...'[url=http://www.sqlservercentral.com/Forums/Topic629528-391-1.aspx][b]http://www.sqlservercentral.com/Forums/Topic629528-391-1.aspx[/b][/url]microsoft.public.sqlserver.programmingThursday, April 24, 2008'Quick puzzle'[url=http://tinyurl.com/7wovgq][b]http://tinyurl.com/7wovgq[/b][/url]microsoft.public.sqlserver.programmingThursday, September 13, 2007 2:27 PM'SQL Query Help: Need to remove consecutive items and just keep the earliest'[url=http://tinyurl.com/7qycl5][b]http://tinyurl.com/7qycl5[/b][/url]comp.databases.ms-sqlserverSep 5, 2008 7:59 am'Grouping similar rows'[url=http://tinyurl.com/3378lk][b]http://tinyurl.com/3378lk[/b][/url][url=http://www.beyondsql.blogspot.com][b]www.beyondsql.blogspot.com[/b][/url][/quote]Not asking you... asking Simon. Those other posts means nothing for the very reason you stated.Sun, 18 Jan 2009 21:25:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]Jeff Moden (1/17/2009)[/b][hr]Simon,I wonder if you'd do me a favor... I realize it's been about 4 months since the last post, but I need to ask... why did you need to do this? I mean, what is the business reason for doing this? Can you explain please?[/quote]Here is a sample of posts that, IMHO, are all similar in nature. They all imply finding a rank (ie. a dense rank) that form groups not explicitly defined by the data. Perhaps you'll find what your looking for among them. There are actually tons of these posts but users struggle with describing them in the subject so they remain elusive as does an agreed upon solution:)T-SQL Black Belt 'Identifying Sections' By:Itzik Ben-Gan [url=http://www.sqlmag.com/Article/ArticleID/95912/sql_server_95912.html ][b]http://www.sqlmag.com/Article/ArticleID/95912/sql_server_95912.html [/b][/url]microsoft.public.sqlserver.programmingJul 5 1999'Difficult SQL Question'[url=http://tinyurl.com/367db2 ][b]http://tinyurl.com/367db2 [/b][/url] microsoft.public.sqlserver.programmingJun 11 2001'SQL challenge: coelesce consecutive and overlapping bookings for a room'[url=http://tinyurl.com/38ebhl ][b]http://tinyurl.com/38ebhl [/b][/url] T-SQL (SS2K5)'How to get start and end value from a...'[url=http://www.sqlservercentral.com/Forums/Topic629528-391-1.aspx][b]http://www.sqlservercentral.com/Forums/Topic629528-391-1.aspx[/b][/url]microsoft.public.sqlserver.programmingThursday, April 24, 2008'Quick puzzle'[url=http://tinyurl.com/7wovgq][b]http://tinyurl.com/7wovgq[/b][/url]microsoft.public.sqlserver.programmingThursday, September 13, 2007 2:27 PM'SQL Query Help: Need to remove consecutive items and just keep the earliest'[url=http://tinyurl.com/7qycl5][b]http://tinyurl.com/7qycl5[/b][/url]comp.databases.ms-sqlserverSep 5, 2008 7:59 am'Grouping similar rows'[url=http://tinyurl.com/3378lk][b]http://tinyurl.com/3378lk[/b][/url][url=http://www.beyondsql.blogspot.com][b]www.beyondsql.blogspot.com[/b][/url]Sat, 17 Jan 2009 21:21:23 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxSimon,I wonder if you'd do me a favor... I realize it's been about 4 months since the last post, but I need to ask... why did you need to do this? I mean, what is the business reason for doing this? Can you explain please?Sat, 17 Jan 2009 19:00:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxSpot on, Gus. :)Fri, 05 Sep 2008 18:51:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxTrust me, I'm not mad or anything. I understand that requirements change and all that.I just didn't have the time to keep up with all the changes on this one, so wasn't able to continue to contribute to it. It's been a busy month for me so far.Please do continue to post questions, comments, etc. It's what all of us are here for. You, me, Jeff, etc., we're all here to help each other out and to get help when we need it.Fri, 05 Sep 2008 09:03:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiRog I did it before you post this thread by creating some temp table, ( because of pressure of deadline). I used your script and it worked perfect too. I am really greatfull for you spending time for newbie(as per jeff). Now, i feel really great about posting the problems on sqlservercentral.com and learned a lot too, how to post it correctly. For Gsquared, i will try to explain the whole scope from the begining next time i post it, don't get mad , this was my first post on the forum. Gsquared was the one who icebreak the task, thanks a lotThanksSimonThu, 04 Sep 2008 14:39:50 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiJeffi got it down twiking a little bit on your logic , the one problem that i was addresing about adding new SRID for all those existing IDs, i solved it by creating couple of temp table. But the Wizard stick was your logicDECLARE @Sequence INT, --Remembers the current sequence @PrevStatusID TINYINT, @ServiceRequestid bigint --Remembers the StatusID from the previous row SELECT @Sequence = 1, @PrevStatusID = 1--===== Do a procedural update using SQL Server's proprietary update UPDATE cap.dbo.Missroute SET @Sequence = SetSequence = CASE WHEN @PrevStatusID <= StatusID THEN @Sequence ELSE @Sequence+1 END, @PrevStatusID = StatusID FROM cap.dbo.MissrouteI got a great feedback from my boss. Thanks a lot Jeff, nailing down this task. You actually show me the direction how to post in readable form in thread upto the completion of the task. Then come the GSquared, Rog pike great guys who helped me on the way. Thanks a lot guysThanksSimonThu, 04 Sep 2008 14:33:26 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]simon phoenix (9/2/2008)[/b][hr]HiJeffIn order to trap the first and last record of each sequence , the scenario we had was based on max(id) and min(id), now if there those id 1,2,3,4,5,6 itself belongs to one specific ID, how do i handle this situation. I will really appreciate for the feedback[/quote]Your introducing the fact that ID varies within Srid. In grouping and joining you now have to take Srid into account. Here is your latest sample data in case anyone wants to play:) Note that ID and DateValue are redundant for the results, either one will due.[code]create table mytable2(Srid int,ID int,DateValue DateTime,Prd char(1),Statusid int, primary key(Srid,ID))goinsert into mytable2SELECT 78, 1 , 'Jan 1 1900 1:00AM', 'A', 1 UNION ALLSELECT 78, 2 , 'Jan 1 1900 2:00AM', 'A', 1 UNION ALLSELECT 78, 3 , 'Jan 1 1900 3:00AM', 'A', 1 UNION ALLSELECT 78, 4 , 'Jan 1 1900 4:00AM', 'A', 1 UNION ALLSELECT 78, 5 , 'Jan 1 1900 5:00AM', 'B', 1 UNION ALLSELECT 78, 6 , 'Jan 1 1900 6:00AM', 'B', 1 UNION ALLSELECT 78, 7 , 'Jan 1 1900 7:00AM', 'B', 1 UNION ALLSELECT 78, 8 , 'Jan 1 1900 8:00AM', 'B', 2 UNION ALLSELECT 78, 9 , 'Jan 1 1900 9:00AM', 'B', 1 UNION ALLSELECT 78, 10, 'Jan 1 1900 10:00AM', 'B', 2 UNION ALLSELECT 78, 11, 'Jan 1 1900 11:00AM', 'B', 2 UNION ALLSELECT 78, 12, 'Jan 1 1900 12:00PM', 'A', 1 UNION ALLSELECT 78, 13, 'Jan 1 1900 1:00PM', 'A', 1 UNION ALLSELECT 78, 14, 'Jan 1 1900 2:00PM', 'A', 1 UNION ALLSELECT 78, 15, 'Jan 1 1900 3:00PM', 'A', 1 UNION ALLSELECT 78, 16, 'Jan 1 1900 4:00PM', 'A', 2 UNION ALLSELECT 79, 1 , 'Jan 1 1900 1:00AM', 'A', 1 UNION ALLSELECT 79, 2 , 'Jan 1 1900 2:00AM', 'A', 1 UNION ALLSELECT 79, 3 , 'Jan 1 1900 3:00AM', 'A', 1 UNION ALLSELECT 79, 4 , 'Jan 1 1900 4:00AM', 'A', 1 UNION ALLSELECT 79, 5 , 'Jan 1 1900 5:00AM', 'B', 1 UNION ALLSELECT 79, 6 , 'Jan 1 1900 6:00AM', 'B', 1 UNION ALLSELECT 79, 7 , 'Jan 1 1900 7:00AM', 'B', 1 UNION ALLSELECT 79, 8 , 'Jan 1 1900 8:00AM', 'B', 2 UNION ALLSELECT 79, 9 , 'Jan 1 1900 9:00AM', 'B', 1 UNION ALLSELECT 79, 10, 'Jan 1 1900 10:00AM', 'B', 2 UNION ALLSELECT 79, 11, 'Jan 1 1900 11:00AM', 'B', 2 UNION ALLSELECT 79, 12, 'Jan 1 1900 12:00PM', 'A', 1 UNION ALLSELECT 79, 13, 'Jan 1 1900 1:00PM', 'A', 1 UNION ALLSELECT 79, 14, 'Jan 1 1900 2:00PM', 'A', 1 UNION ALLSELECT 79, 15, 'Jan 1 1900 3:00PM', 'A', 1 UNION ALLSELECT 79, 16, 'Jan 1 1900 4:00PM', 'A', 2 [/code]Sql solution. Similar to my previous one with addition of Srid.[code]WITH C AS( SELECT A.Srid,A.ID,A.DateValue,A.Prd,A.Statusid,B.Statusid AS PriorStatusid FROM mytable2 AS A LEFT JOIN mytable2 AS B ON (A.Srid=B.Srid) And (A.ID=B.ID+1)), E AS( SELECT D.Srid,D.ID,D.DateValue,D.Prd,D.Statusid, SUM(CASE WHEN E.PriorStatusid=2 THEN 1 ELSE 0 END)+1 AS Setsequence FROM C AS D JOIN C AS E ON (D.Srid=E.Srid) AND (E.ID BETWEEN 0 AND D.ID) GROUP BY D.Srid,D.ID,D.DateValue,D.Prd,D.Statusid)SELECT E.Srid,E.ID,CAST(E.DateValue AS VARCHAR(20)) AS DateValue,E.Prd,E.Statusid,E.Setsequence,-- Number each Setsequence from 1-N and choose 1st one for Setfirst. All other values/rows set to 0.CASE WHEN (ROW_NUMBER()OVER(PARTITION BY E.Srid,E.Setsequence ORDER BY E.ID)>1) THEN 0 ELSE 1 END AS Setfirst,E.Statusid-1 AS SetlastFROM EORDER BY E.Srid,E.ID[/code]Setsequence is within Srid, in other words Setsequence starts over at 1 within each Srid. I wonder how many people eat something like this up? Look at it, come back in twenty minutes and tell me it's still obvious what it's doing:)Srid ID DateValue Prd Statusid Setsequence Setfirst Setlast---- ---- -------------------- ---- -------- ----------- -------- -------78 1 Jan 1 1900 1:00AM A 1 1 1 078 2 Jan 1 1900 2:00AM A 1 1 0 078 3 Jan 1 1900 3:00AM A 1 1 0 078 4 Jan 1 1900 4:00AM A 1 1 0 078 5 Jan 1 1900 5:00AM B 1 1 0 078 6 Jan 1 1900 6:00AM B 1 1 0 078 7 Jan 1 1900 7:00AM B 1 1 0 078 8 Jan 1 1900 8:00AM B 2 1 0 178 9 Jan 1 1900 9:00AM B 1 2 1 078 10 Jan 1 1900 10:00AM B 2 2 0 178 11 Jan 1 1900 11:00AM B 2 3 1 178 12 Jan 1 1900 12:00PM A 1 4 1 078 13 Jan 1 1900 1:00PM A 1 4 0 078 14 Jan 1 1900 2:00PM A 1 4 0 078 15 Jan 1 1900 3:00PM A 1 4 0 078 16 Jan 1 1900 4:00PM A 2 4 0 179 1 Jan 1 1900 1:00AM A 1 1 1 079 2 Jan 1 1900 2:00AM A 1 1 0 079 3 Jan 1 1900 3:00AM A 1 1 0 079 4 Jan 1 1900 4:00AM A 1 1 0 079 5 Jan 1 1900 5:00AM B 1 1 0 079 6 Jan 1 1900 6:00AM B 1 1 0 079 7 Jan 1 1900 7:00AM B 1 1 0 079 8 Jan 1 1900 8:00AM B 2 1 0 179 9 Jan 1 1900 9:00AM B 1 2 1 079 10 Jan 1 1900 10:00AM B 2 2 0 179 11 Jan 1 1900 11:00AM B 2 3 1 179 12 Jan 1 1900 12:00PM A 1 4 1 079 13 Jan 1 1900 1:00PM A 1 4 0 079 14 Jan 1 1900 2:00PM A 1 4 0 079 15 Jan 1 1900 3:00PM A 1 4 0 079 16 Jan 1 1900 4:00PM A 2 4 0 1RAC solution, 1 pass over data no joins no recursive calls. This is simpler, at least to me:):) Hay its got fewer lines than sql solution. It's also much more in line with Jeffs thinking on using Update.[code]Exec Rac@TRANSFORM='_dummy_', -- Plays no part in computations.@ROWS='Srid & ID & DateValue & Prd & Statusid',@ROWSORT='Srid & ID',@PVTCOL='Report Mode', -- This exec is for grouped data, not a crosstab.@FROM='mytable2',@ROWBREAK='n',@DEFAULTS1='y',@RACHECK='y',@SHELL='n',-- There are 2 running sums over the table (independent of Srid/ID. First run forms-- groups (Setsequence), 2nd run is a counter from 0 to N of the rows in each group (Setsequence).-- The 2nd run will be used to flag the start of a new Setsequence. This is Setfirst.@ROWRUNS='Dumy^CASE WHEN PRIOR.Statusid=2 THEN 1 ELSE 0 END^(Dumy) & Dumy^CASE WHEN prior.Srid is null or PRIOR.Statusid=2 THEN -1*_CURRENTRUN_ else 1 END^(Dumy)', -- Function racrunfld extracts a particular run into its own colunn.@select='SELECT _ROWS_,dbo.racrunfld(Runs,1)+1 AS Setsequence, CASE WHEN dbo.racrunfld(Runs,2)>0 THEN 0 ELSE 1 END AS Setfirst, Statusid-1 AS Setlast FROM rac ORDER BY rd' [/code]Here Setsequence increments over Srid. But, like sql solution, you should include Srid in any further grouping.Srid ID DateValue Prd Statusid Setsequence Setfirst Setlast------ ---- ------------------- ---- -------- ----------- ----------- -----------78 1 Jan 1 1900 1:00AM A 1 1 1 078 2 Jan 1 1900 2:00AM A 1 1 0 078 3 Jan 1 1900 3:00AM A 1 1 0 078 4 Jan 1 1900 4:00AM A 1 1 0 078 5 Jan 1 1900 5:00AM B 1 1 0 078 6 Jan 1 1900 6:00AM B 1 1 0 078 7 Jan 1 1900 7:00AM B 1 1 0 078 8 Jan 1 1900 8:00AM B 2 1 0 178 9 Jan 1 1900 9:00AM B 1 2 1 078 10 Jan 1 1900 10:00AM B 2 2 0 178 11 Jan 1 1900 11:00AM B 2 3 1 178 12 Jan 1 1900 12:00PM A 1 4 1 078 13 Jan 1 1900 1:00PM A 1 4 0 078 14 Jan 1 1900 2:00PM A 1 4 0 078 15 Jan 1 1900 3:00PM A 1 4 0 078 16 Jan 1 1900 4:00PM A 2 4 0 179 1 Jan 1 1900 1:00AM A 1 5 1 079 2 Jan 1 1900 2:00AM A 1 5 0 079 3 Jan 1 1900 3:00AM A 1 5 0 079 4 Jan 1 1900 4:00AM A 1 5 0 079 5 Jan 1 1900 5:00AM B 1 5 0 079 6 Jan 1 1900 6:00AM B 1 5 0 079 7 Jan 1 1900 7:00AM B 1 5 0 079 8 Jan 1 1900 8:00AM B 2 5 0 179 9 Jan 1 1900 9:00AM B 1 6 1 079 10 Jan 1 1900 10:00AM B 2 6 0 179 11 Jan 1 1900 11:00AM B 2 7 1 179 12 Jan 1 1900 12:00PM A 1 8 1 079 13 Jan 1 1900 1:00PM A 1 8 0 079 14 Jan 1 1900 2:00PM A 1 8 0 079 15 Jan 1 1900 3:00PM A 1 8 0 079 16 Jan 1 1900 4:00PM A 2 8 0 1>i guess i do not completely know how to explain the problem properly!The industry is so obsessed with coding that its forgotten about framing questions adequately and with clarity! Words still matter. Hardly your fault:)[url=http://www.rac4sql.net][b]www.rac4sql.net[/b][/url][url=http://www.beyondsql.blogspot.com][b]www.beyondsql.blogspot.com[/b][/url]Thu, 04 Sep 2008 04:16:28 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHeh... understood... I think they call that "agile" programming and I hate it. :)You said in your previous post that ID's 1-6 were singleton's... looking at the data you provided, how would one know? And what about the singleton "2" at both ID = 11 rows?Wed, 03 Sep 2008 17:16:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiJeff, GusI was not trying to mislead you guys by properly not explaining the whole scope at the begining. I thought giving the small portion of scope will help instead of explaining the big picture of Scope. I am extremly sorry, if that causes any problem to you guys, that was not an intended behavior, i guess i do not completely know how to explain the problem properly!ThanksSimonWed, 03 Sep 2008 17:03:38 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxThe scope-creep on this one is why I gave up on it. The original problem, as stated, has been solved for a long time, but it turned out the original problem wasn't actually what was needed.Wed, 03 Sep 2008 08:49:56 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHeh... you keep adding scope to the problem, Simon. Not a problem but it would be nice to know the whole problem at once. For example, where did the SRID column suddenly come from and what's up with ID 11 for both SRID's?Also, I don't really understand your latest question... 1-6 in both SRID's have a SetSequence of 1... by your original definition, the sequence doesn't end until a 2 occurs...Tue, 02 Sep 2008 20:28:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiJeffIn order to trap the first and last record of each sequence , the scenario we had was based on max(id) and min(id), now if there those id 1,2,3,4,5,6 itself belongs to one specific ID, how do i handle this situation. I will really appreciate for the feedbackSrid ID DateValue Prd Statusid SetSequence78 1 01:00.0 A 1 178 2 02:00.0 A 1 178 3 03:00.0 A 1 178 4 04:00.0 A 1 178 5 05:00.0 B 1 178 6 06:00.0 B 1 178 7 07:00.0 B 1 178 8 08:00.0 B 2 178 9 09:00.0 B 1 278 10 10:00.0 B 2 278 11 11:00.0 B 2 278 12 12:00.0 A 1 378 13 13:00.0 A 1 378 14 14:00.0 A 1 378 15 15:00.0 A 1 378 16 16:00.0 A 2 379 1 01:00.0 A 1 479 2 02:00.0 A 1 479 3 03:00.0 A 1 479 4 04:00.0 A 1 479 5 05:00.0 B 1 479 6 06:00.0 B 1 479 7 07:00.0 B 1 479 8 08:00.0 B 2 479 9 09:00.0 B 1 579 10 10:00.0 B 2 579 11 11:00.0 B 2 579 12 12:00.0 A 1 579 13 13:00.0 A 1 579 14 14:00.0 A 1 579 15 15:00.0 A 1 579 16 16:00.0 A 2 5ThanksSimonTue, 02 Sep 2008 10:43:15 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHeh...I appreciate the advertised "simplicity" of your product, but don't try to justify it by posting a bunch of bloated links with [b][i]bad [/i][/b]information in them. ;)Both the UPDATE method of SET @variable = column = expression and SELECT (or SET) @variable = @variable op expression [b][i]ARE[/i][/b] documented in Books Online. Just because they don't have a specific example for @variable = @variable op expression, doesn't mean it isn't documented... heh... it just means that you actually have to know how to use Books Online. ;)First, on the subject of the UPDATE method, from Books Online...[quote][b][size="3"]UPDATE[/size][/b] Changes existing data in a table.SyntaxUPDATE { table_name WITH ( [ ...n ] ) | view_name | rowset_function_limited } SET { column_name = { expression | DEFAULT | NULL } | @variable = expression | [b][color="RED"]@variable = column = expression [/color][/b]} [ ,...n ] [/quote]And, now, on the subject of SELECT @variable = @variable or SELECT @variable = @variable op expression... again, from Books Online...[quote][b][size="3"]SELECT @local_variable[/size][/b]Specifies that the given local variable (created using DECLARE @local_variable) should be set to the specified expression.It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable. For more information, see SET @local_variable.SyntaxSELECT { [b]@local_variable = [/b][b][color="RED"]expression [/color][/b]} [ ,...n ] Arguments@local_variable Is a declared variable for which a value is to be assigned.expression[b][color="RED"]Is [size="3"]any [/size]valid Microsoft® SQL Server™ expression[/color][/b], including a scalar subquery.[/quote]... and on the subject of SET @variable = @variable or SET @variable = @variable op expression... again, from Books Online...[quote]SET @local_variableSets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value.SyntaxSET { { [b]@local_variable = [color="RED"]expression [/color][/b]} | { @cursor_variable = { @cursor_variable | cursor_name | { CURSOR [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] } } } } Arguments@local_variableIs the name of a variable of any type except cursor, text, ntext, or image. Variable names must begin with one at sign (@). Variable names must conform to the rules for identifiers. For more information, see Using Identifiers.[b][color="RED"]expressionIs [size="3"]any [/size]valid Microsoft® SQL Server™ expression.[/color][/b][/quote]Further... (also from books online)...[quote][b][size="3"]Expressions[/size][/b]A combination of symbols and operators that Microsoft® SQL Server™ evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.[b]Syntax[/b]{ constant | scalar_function | [ alias. ] column | [b][color="RED"]local_variable [/color][/b] | ( expression ) | ( scalar_subquery ) | { unary_operator } expression | expression { binary_operator } expression }[b]Arguments[/b].........[b][color="RED"](expression)[/color][/b][b][color="RED"]Is [size="3"]any [/size]valid SQL Server expression as defined in this topic.[/color][/b] The parentheses are grouping operators that ensure that all the operators in the expression within the parentheses are evaluated before the resulting expression is combined with another.[/quote]The operation of a clustered index [b][i]IS[/i][/b] documented in Books Online (way too long to post here) and supports the method I used. Many have tried to prove it won't work the way I've described... so far, all have failed. It's important to note that it may not work as [i]some expect [/i]on partioned tables/views, but that's not a failure if you read about clustered indexes... that's the expected behaviour and I wouldn't use the method across the full length of partitioned table. I agree that ORDER BY is important to how things are [i]displayed[/i], but not as to the order they are [i]processed[/i].This isn't Oracle and SQL Server's windowing functions do not currently operate in the same fashion. At this point, no one cares whether SQL Server is a "true" RDBMS or not... the OP's only care if someone can solve their problem using SQL Server. ;)Last, but certainly not least, I wish you all of the luck in the world with your product. The advantage of learning how to do all of this stuff in SQL Server is that, no matter where in the world you go, you can do these things in SQL Server whether or not some 3rd party software is available or not. Yes, a 3rd party product may make some things easier, but that 3rd party product is not available at all installations and the owners of those installations may simply not allow such 3rd party products anywhere near their databases. Bosses don't like it at all when you say "I can't do it because I need to buy (some product)" especially when the boss knows that it CAN be done without that 3rd party product.Mon, 01 Sep 2008 10:58:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]Jeff Moden (8/30/2008)[/b][hr][code]--===== Show first and last PRD and date of each SetSequence SELECT yt.SetSequence, MAX(CASE WHEN d.MinID = yt.ID THEN PRD END) AS MinPrd, MAX(CASE WHEN d.MaxID = yt.ID THEN PRD END) AS MaxPrd, MAX(CASE WHEN d.MinID = yt.ID THEN DateValue END) AS MinDateValue, MAX(CASE WHEN d.MaxID = yt.ID THEN DateValue END) AS MaxDateValue FROM #YourTable yt INNER JOIN (SELECT SetSequence, MIN(ID) AS MinID, MAX(ID) AS MaxID FROM #YourTable GROUP BY SetSequence)d ON (yt.ID = d.MinID OR yt.ID = d.MaxID) GROUP BY yt.SetSequence[/code][/quote] The real intent of the sql window (full OVER) is to make things much simpler. Part of the fallout is that problems like this can be solved 'without' any joins, just a single pass thru the data. For example, in Oracle this problem can be solved, including indicating first and last PRD, using only window functions in one pass. I'll bore people with the Rac version (2 passes, no joins):)[code]Exec Rac@TRANSFORM='_dummy_', -- Plays no part in computations.@ROWS='ID & DateValue(d) & Prd & Statusid',@PVTCOL='Report Mode', -- This exec is for grouped data, not a crosstab.@FROM='#mytable',@DEFAULTS1='y',@RACHECK='y',@SHELL='n',-- @ROWRUNS is a running sum that does not involve a @TRANSFORM, hense Dumy [Smile] @ROWRUNS='Dumy^CASE WHEN PRIOR.Statusid=2 THEN 1 ELSE 0 END^(Dumy)',@ROWRUNSLABEL='DenseRank',-- The @SELECT parameter contains a recursive call to Rac with the result of-- the first Rac execute saved in a local temporary table (#T1). The server will drop-- drop table #T1 at the end of the recursive (2nd) execute.@SELECT='SELECT 1*ID AS ID,CAST(DateValue as DATETIME) AS DateValue,Prd, 1*Statusid AS Statusid, (1*DenseRank)+1 AS Setsequence /* Add 1 to rank to start at 1 instead of 0. */ INTO #T1 FROM rac Exec Rac @TRANSFORM=~_dummy_~, /* The sequence of columns facilates data manipulation (first/last counters) */ @ROWS=~Setsequence & ID & DateValue(d) & Prd & Statusid~, /* Process the rows in the order of ID. */ @ROWSORT=~ID~, @PVTCOL=~Report Mode~, @FROM=~#T1~, @ROWBREAK=~n~,@DEFAULTS1=~y~,@RACHECK=~y~,@SHELL=~n~, @ROWCOUNTERS=~Setsequence{Setfirst}~,/* Row numbers (1-N) within each Setsequence */ @LASTCOUNTERS=~Setsequence{Setlast}~,/* Flag last Setsequence row within each group (Setsequence) */ @COUNTERDATATYPE=~int~, @SELECT=~SELECT ID,DateValue,Prd,Statusid,Setsequence, /* Set all row numbers of Setfirst > 1 to 0 */ CASE WHEN Setfirst>1 THEN 0 ELSE 1 END AS Setfirst,Setlast FROM rac ORDER BY rd~'[/code] ID DateValue Prd Statusid Setsequence Setfirst Setlast---- ------------------- ---- -------- ----------- ----------- -----------1 Oct 17 2007 12:01AM A 1 1 1 02 Oct 17 2007 12:02AM A 1 1 0 03 Oct 17 2007 12:03AM A 1 1 0 04 Oct 17 2007 12:04AM A 1 1 0 05 Oct 17 2007 12:05AM B 1 1 0 06 Oct 17 2007 12:06AM B 1 1 0 07 Oct 17 2007 12:07AM B 1 1 0 08 Oct 17 2007 12:08AM B 2 1 0 19 Oct 17 2007 12:09AM B 1 2 1 010 Oct 17 2007 12:10AM B 2 2 0 111 Oct 17 2007 12:11AM A 1 3 1 012 Oct 17 2007 12:12AM A 1 3 0 013 Oct 17 2007 12:13AM A 1 3 0 014 Oct 17 2007 12:14AM A 1 3 0 015 Oct 17 2007 12:15AM A 2 3 0 1[quote][b]Jeff Moden (8/30/2008)[/b][hr]Now, the clustered index makes it easy using the very same technique in the following article...[url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]--===== Do a procedural update using SQL Server's proprietary update[code] UPDATE #YourTable SET @Sequence = SetSequence = CASE WHEN @PrevStatusID <= StatusID THEN @Sequence ELSE @Sequence+1 END, @PrevStatusID = StatusID FROM #YourTable WITH(INDEX(PK_YourTable_ID))--===== Show the results SELECT * FROM #YourTable[/code]Questions? :)[/quote]That stuff! So new users are fighting an old war:) Some of us we're trying to get to what they were 'really' doing years ago. For some interesting deep background I suggest you peruse these threads (Pele is I :) :microsoft.public.sqlserver.programmingMar 7 2003"transpose problem"[url=http://tinyurl.com/69lzz][b]http://tinyurl.com/69lzz[/b][/url](Especially the replies by Bill Hollinshead [MSFT].)microsoft.public.sqlserver.programmingJan 23 2003"Undocumented operation"[url=http://tinyurl.com/6jprz9][b]http://tinyurl.com/6jprz9[/b][/url](Especially the reply by Hal Berenson [MSFT].)I'm not going to rain on your parade. As I've implied this type of processing has been fulled usurped by standard sql (window functions) they should implement instead of trying to turn the server into a LINQ slave:) This type of 'cursor' is, after all, underneath the covers of the window functions."The Sql ranking OVERture"[url=http://beyondsql.blogspot.com/2008/04/sql-ranking-overture.html][b]http://beyondsql.blogspot.com/2008/04/sql-ranking-overture.html[/b][/url]And yes,it still upsets people especially those who want to believe sql server is a true 'relational' db. Because of its 'real' intent this stuff will probably be there forever:) As for an 'ironclad' guarantee who knows. Check out this recent article:"No Seatbelt - Expecting Order without ORDER BY"by Conor Cunningham [MSFT] [url=http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx][b]http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx[/b][/url]Suffice it to say some people (moi) have successfully used this technique without any problems. (I will also point that SAS (Statistical Analysis System) has had this same type of processing for almost 30 years!) :)[url=http://www.rac4sql.net][b]www.rac4sql.net[/b][/url][b][url=http://www.beyondsql.blogspot.com]www.beyondsql.blogspot.com[/url][/b]Sun, 31 Aug 2008 16:10:47 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]simon phoenix (8/31/2008)[/b][hr]HiJeffEverything worked out perfect, exactly what i wanted. After a long time , i am going to have good night sleep. I am not scared of going to work on Tuesday now:) GBYThanksSimon[/quote]Heh... sleep is good! Thanks for the feedback, Simon. And, never be afraid to go to work because of something like this... "they" couldn't have done it without a little help from friends or a cursor, either. :DI am concerned about some questions that you'll likely be asked (I've been through this a hundred times :P). Do you understand how this was done well enough to explain it to others? The thing about using the clustered index the way I did has been and will be balked at by many non-believers. Even the DBA may balk at it. Since extraordinary claims require extraordinary proof, you may want to refer them to the article I previously referred you to at [url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url] . The problem that most will talk about starts in the section called "[b]Gail's "Merry-go-Round" Index[/b]". Basically, every one has been "brought up" to believe that the only way you can guarantee the "[b][i]processing order[/i][/b]" of something is with an ORDER BY and will likely fight tooth'n'nail with you that there's no other way. The proof that the clustered index method works as advertised is in the section called "[b]Stop the Merry-go-Round, I wanna get off! (Final Test Data)[/b]"... the code shows that even a table that's had thousands of "out of order" inserts (according to the clustered index) is still in correct sequential logic processing order no matter what, page splits included.Trust me... if they see the code, they're gonna challenge it and you'll need to be able to not only explain it, but demonstrate that it works and the article does just that.Sun, 31 Aug 2008 09:14:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiJeffEverything worked out perfect, exactly what i wanted. After a long time , i am going to have good night sleep. I am not scared of going to work on Tuesday now:) GBYThanksSimonSun, 31 Aug 2008 00:04:24 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[code]--===== Show first and last PRD and date of each SetSequence SELECT yt.SetSequence, MAX(CASE WHEN d.MinID = yt.ID THEN PRD END) AS MinPrd, MAX(CASE WHEN d.MaxID = yt.ID THEN PRD END) AS MaxPrd, MAX(CASE WHEN d.MinID = yt.ID THEN DateValue END) AS MinDateValue, MAX(CASE WHEN d.MaxID = yt.ID THEN DateValue END) AS MaxDateValue FROM #YourTable yt INNER JOIN (SELECT SetSequence, MIN(ID) AS MinID, MAX(ID) AS MaxID FROM #YourTable GROUP BY SetSequence)d ON (yt.ID = d.MinID OR yt.ID = d.MaxID) GROUP BY yt.SetSequence[/code]Sat, 30 Aug 2008 23:43:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiJeff Probably the last question, since you got everthing sort out for me, along with min(datevlue) and max(datevalue) for everysequence , i would like to get the corresponding 'Prd'. I tried to include the'Prd' on your provided select statment but since 'Prd' has to be included in group by too, result was wacked! Could you please help on this almost last request:)ThanksSimonSat, 30 Aug 2008 21:38:01 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]simon phoenix (8/30/2008)[/b][hr]Now only job is to trap the begining and end of those distinct sequence and i think i can do it. [/quote]Missed that... sorry. Easy to do now that the SetSequence is done...[code]--===== Show first and last date of each SetSequence SELECT SetSequence, MIN(DateValue) AS StartDateTime, MAX(DateValue) AS EndDateTime FROM #YourTable GROUP BY SetSequence[/code]Sat, 30 Aug 2008 20:50:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]simon phoenix (8/30/2008)[/b][hr]HiRogYou are the SQl Guru, Thanks a lot , i really appreciat it, it was just bugging my head since last week. Now only job is to trap the begining and end of those distinct sequence and i think i can do it. If i ran into trouble , i will post it again, please look into this thread for few more days, i am really close in getting my task done with the help of great guys like you, Gsquared and jeff. i can't explain how greatful i am to find good people like you guys existGod bless you Rog, Jeff and GsquaredThanksSimon ----------Happy Labor Day----------[/quote]Simon, "you da man!" Thanks for the awesome thought. Happy Labor Day!Sat, 30 Aug 2008 20:30:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHeh... ok... my turn. :) And sorry it took me so long to get to this problem...First, in order to be fast and short, this fun problem [b][i]does [/i][/b] actually require some procedureal code, but we won't be using a Cursor or a While loop. And, we don't need extra columns nor do we need to buy a special product to get it done. AND, we don't need to self join to the same table 4 times to do it either. All we need is what appears to be the correct logical clustered index which you probably already have anyway.First, the test data... do read the comments please... this little trick requires a clustered index on the ID column...[code]drop table #yourtablego--===== Setup the demonstration table... -- I'm assuming that the ID column is the CLUSTERED PRIMARY KEY -- and that it's named PK_YourTable_ID. Please adjust as necessary. -- I'm also assuming that it has a "SetSequence" column. CREATE TABLE #YourTable( ID INT IDENTITY(1,1), DateValue DATETIME, Prd CHAR(1), StatusID TINYINT, SetSequence INT, CONSTRAINT PK_YourTable_ID PRIMARY KEY CLUSTERED (ID)) INSERT INTO #YourTable (DateValue,Prd,StatusID) SELECT '2007-10-17 00:01:00.000','A','1' UNION ALL SELECT '2007-10-17 00:02:00.000','A','1' UNION ALL SELECT '2007-10-17 00:03:00.000','A','1' UNION ALL SELECT '2007-10-17 00:04:00.000','A','1' UNION ALL SELECT '2007-10-17 00:05:00.000','B','1' UNION ALL SELECT '2007-10-17 00:06:00.000','B','1' UNION ALL SELECT '2007-10-17 00:07:00.000','B','1' UNION ALL SELECT '2007-10-17 00:08:00.000','B','2' UNION ALL SELECT '2007-10-17 00:09:00.000','B','1' UNION ALL SELECT '2007-10-17 00:10:00.000','B','2' UNION ALL SELECT '2007-10-17 00:11:00.000','A','1' UNION ALL SELECT '2007-10-17 00:12:00.000','A','1' UNION ALL SELECT '2007-10-17 00:13:00.000','A','1' UNION ALL SELECT '2007-10-17 00:14:00.000','A','1' UNION ALL SELECT '2007-10-17 00:15:00.000','A','2'GO--===== Show the content of the test table SELECT * FROM #YourTable[/code]The key to this problem is very much like "trailing edge digital logic". We don't actually care where the 1's and 2's are... we only care when the StatusID drops in value. To translate into pseudo code, when the StatusID of the previous row is LESS THAN OR EQUAL TO the current row, the SetSequence will remain the same, otherwise, we need to add 1 to the SetSequence.Now, the clustered index makes it easy using the very same technique in the following article...[url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]... here's the code that does it... at a rate of a million rows every 7 seconds... :P[code]--===== Declare some necessary variablesDECLARE @Sequence INT, --Remembers the current sequence @PrevStatusID TINYINT --Remembers the StatusID from the previous row SELECT @Sequence = 1, @PrevStatusID = 1--===== Do a procedural update using SQL Server's proprietary update UPDATE #YourTable SET @Sequence = SetSequence = CASE WHEN @PrevStatusID <= StatusID THEN @Sequence ELSE @Sequence+1 END, @PrevStatusID = StatusID FROM #YourTable WITH(INDEX(PK_YourTable_ID))--===== Show the results SELECT * FROM #YourTable[/code]Questions? :)Sat, 30 Aug 2008 20:27:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiRogYou are the SQl Guru, Thanks a lot , i really appreciat it, it was just bugging my head since last week. Now only job is to trap the begining and end of those distinct sequence and i think i can do it. If i ran into trouble , i will post it again, please look into this thread for few more days, i am really close in getting my task done with the help of great guys like you, Gsquared and jeff. i can't explain how greatful i am to find good people like you guys existGod bless you Rog, Jeff and GsquaredThanksSimon ----------Happy Labor Day----------Sat, 30 Aug 2008 20:05:39 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxSorry... My bad... Roger's too. That syntax, starting at ROWS, isn't part of T-SQL in SQL Server 2005.Sat, 30 Aug 2008 19:25:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]simon phoenix (8/30/2008)[/b][hr]HiRog, JeffI have problem running this statementSELECT A.ID,A.DateValue,A.Prd,A.Statusid,Sum(CASE WHEN B.Statusid=2 THEN 1 ELSE 0 END) OVER(ORDER BY A.ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-- this line throws error AS Setsequence FROM #mytable AS A LEFT JOIN #mytable AS B ON A.ID=B.ID+1Msg 102, Level 15, State 1, Line 4Incorrect syntax near 'ROWS'.ThanksSimon[/quote]Sorry for any confusion. The above form of OVER is NOT supported in S2k5. But RAC does run in S2k5 :) Anyway here is a t-sql solution that will simulate the running sum of OVER. Actually it's not as bad as I perhaps made it out to be but its no substitute for the real thing. I'm using the same sample data used in RAC.[code]WITH C AS( SELECT A.ID,A.DateValue,A.Prd,A.Statusid,B.Statusid AS PriorStatusid FROM #mytable AS A LEFT JOIN #mytable AS B ON A.ID=B.ID+1) select D.ID,D.DateValue,D.Prd,D.Statusid, Sum(CASE WHEN E.PriorStatusid=2 THEN 1 ELSE 0 END)+1 AS Setsequence FROM C AS D JOIN C AS E ON E.ID BETWEEN 0 AND D.ID GROUP BY D.ID,D.DateValue,D.Prd,D.Statusid ORDER BY D.ID[/code]Note there are 2 joins, one to access the prior row Statusid and one to form the rows for grouping. It's these joins that OVER (and RAC) eliminate. For large data sets performance could be a problem here. But that's another issue :)[url=http://www.rac4sql.net][b]www.rac4sql.net[/b][/url][url=http://www.beyondsql.blogspot.com ][b]www.beyondsql.blogspot.com[/b][/url]Sat, 30 Aug 2008 19:19:03 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiRog, JeffI have problem running this statementSELECT A.ID,A.DateValue,A.Prd,A.Statusid,Sum(CASE WHEN B.Statusid=2 THEN 1 ELSE 0 END) OVER(ORDER BY A.ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-- this line throws error AS Setsequence FROM #mytable AS A LEFT JOIN #mytable AS B ON A.ID=B.ID+1Msg 102, Level 15, State 1, Line 4Incorrect syntax near 'ROWS'.ThanksSimonSat, 30 Aug 2008 17:24:18 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxUmmm... I thought Rog gave a solution that didn't include RAC... take a look at his post again. ;)Sat, 30 Aug 2008 16:50:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiRogThanks for your input on this!Is there any other way to get this result without using RAC? thats the exact result that i was lookingfor . I am using sql2k5 , that is also a big problem to use RACThankssimonSat, 30 Aug 2008 15:54:18 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxThe common problem of dividing data into groups boils down to creating a dense rank. Here we want a dense rank on Statusid but ordered by ID (or DateValue). This problem should be easily solved using the Dense_Rank() function in S2005. Unfortunately all the ranking functions assume the target of the rank is also its order. The order cannot be independent of the target. This dependency was intended to be solved by the full implementation of the OVER statement in sql-99. This is known as the sql window. With the full OVER the target of a rank and its order can be independently specified. The Dense_Rank() function is actually a short hand for a generalized dense rank (using OVER). This rank is based on a 'running sum' over the target column (or more generally an expression involving the column) and can be ordered by another column.The running sum is equivalent to a dense rank.To get this type of dense rank you simply have to state the rule that increments the run. This is the same as deciding what rule bounds each rank (group). In this problem each rank is bounded by a value of 1 followed by a 2. The run is incremented whenever there is a Statusid change from a some value other than 1 (2,null) to 1. Here all you have to do is check the prior row value of Statusid. If the prior row (based in the order of ID) is a 2 the current row starts a new group (the running sum is incremented by 1).Sample data.[code]CREATE TABLE #mytable (ID INT PRIMARY KEY, DateValue DATETIME, Prd VARCHAR(10), Statusid INT ) INSERT INTO #mytable (ID, DateValue, Prd, Statusid) SELECT 1, '2007-10-17 00:01:00.000','A',1 UNION ALL SELECT 2, '2007-10-17 00:02:00.000','A',1 UNION ALL SELECT 3, '2007-10-17 00:03:00.000','A',1 UNION ALL SELECT 4, '2007-10-17 00:04:00.000','A',1 UNION ALL SELECT 5, '2007-10-17 00:05:00.000','B',1 UNION ALL SELECT 6, '2007-10-17 00:06:00.000','B',1 UNION ALL SELECT 7, '2007-10-17 00:07:00.000','B',1 UNION ALL SELECT 8, '2007-10-17 00:08:00.000','B',2 UNION ALL SELECT 9, '2007-10-17 00:09:00.000','B',1 UNION ALL SELECT 10,'2007-10-17 00:10:00.000','B',2 UNION ALL SELECT 11,'2007-10-17 00:11:00.000','A',1 UNION ALL SELECT 12,'2007-10-17 00:12:00.000','A',1 UNION ALL SELECT 13,'2007-10-17 00:13:00.000','A',1 UNION ALL SELECT 14,'2007-10-17 00:14:00.000','A',1 UNION ALL SELECT 15,'2007-10-17 00:15:00.000','A',2 UNION ALL [/code]An outer join would make it easy to check the prior Statusid.SELECT A.ID,A.DateValue,A.Prd,A.Statusid,B.ID,B.StatusidFROM #mytable AS A LEFT JOIN #mytable AS BON A.ID=B.ID+1ORDER BY A.ID Using the full implemention of OVER (sql-99 window) and the Sum aggregateyou could form the groups, get a dense rank, with one pass thru the join:SELECT A.ID,A.DateValue,A.Prd,A.Statusid, Sum(CASE WHEN B.Statusid=2 THEN 1 ELSE 0 END) OVER(ORDER BY A.ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Setsequence FROM #mytable AS A LEFT JOIN #mytable AS B ON A.ID=B.ID+1Even the join could be eliminated by using a LAG function of OVER (gets theprevious ID value) SELECT ID,DateValue,Prd,Statusid, Sum(CASE WHEN LAG(ID,1)=2 THEN 1 ELSE 0 END) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Setsequence FROM #mytable Well all this is pie in the sky for sql server because MS for some other worldyreason(s) decided the full implementation of OVER could wait. The are various ways to simulate OVER such as combinations of ranking functions/counts, updates, subqueries etc. None are as simple and straightforward as OVER. Of course for sql server I prefer to use Rac:)[code]Exec Rac@TRANSFORM='_dummy_', -- Plays no part in computations.@ROWS='ID & DateValue(d) & Prd & Statusid',@PVTCOL='report mode',@FROM='#mytable',@DEFAULTS1='y',@RACHECK='y',@SHELL='n',-- @ROWRUNS is a running sum that does not involve a @TRANSFORM, hense Dumy:)@ROWRUNS='Dumy^CASE WHEN PRIOR.Statusid=2 THEN 1 ELSE 0 END^(Dumy)',@ROWRUNSLABEL='DenseRank',-- Add 1 to the rank to start at 1 (instead of 0).@SELECT='SELECT _ROWS_,(1*DenseRank)+1 AS Setsequence FROM rac ORDER BY rd' [/code] ID DateValue Prd Statusid Setsequence------ ------------------- ---- -------- -----------1 Oct 17 2007 12:01AM A 1 12 Oct 17 2007 12:02AM A 1 13 Oct 17 2007 12:03AM A 1 14 Oct 17 2007 12:04AM A 1 15 Oct 17 2007 12:05AM B 1 16 Oct 17 2007 12:06AM B 1 17 Oct 17 2007 12:07AM B 1 18 Oct 17 2007 12:08AM B 2 19 Oct 17 2007 12:09AM B 1 210 Oct 17 2007 12:10AM B 2 211 Oct 17 2007 12:11AM A 1 312 Oct 17 2007 12:12AM A 1 313 Oct 17 2007 12:13AM A 1 314 Oct 17 2007 12:14AM A 1 315 Oct 17 2007 12:15AM A 2 3You could flag 1st and last rows of each group or whatever. But I think I'll stop here:)[url=http://www.rac4sql.net][b]www.rac4sql.net[/b][/url][url=http://www.beyondsql.blogspot.com][b]www.beyondsql.blogspot.com[/b][/url]Fri, 29 Aug 2008 22:06:55 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiGsquaredi copied the previous data from sql pane and they are not aligining to each other, i think you can estimate its order. Please let me know if its not readableThanksSimonFri, 29 Aug 2008 16:33:50 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiGSquaredAfter runnig your both the query, for set and sequece, this is what i gotID DateValue Prd statusid setfirst setlast setid setsequence1 2007-10-17 00:01:00.000 A 1 1 0 1 12 2007-10-17 00:02:00.000 A 1 0 0 2 13 2007-10-17 00:03:00.000 A 1 0 0 0 14 2007-10-17 00:04:00.000 A 1 0 0 0 25 2007-10-17 00:05:00.000 B 1 1 0 0 36 2007-10-17 00:06:00.000 B 1 0 0 0 47 2007-10-17 00:07:00.000 B 1 0 0 0 58 2007-10-17 00:08:00.000 B 2 0 1 0 69 2007-10-17 00:09:00.000 B 1 1 0 0 710 2007-10-17 00:10:00.000 B 2 0 1 0 811 2007-10-17 00:11:00.000 A 1 0 0 0 912 2007-10-17 00:12:00.000 A 1 0 0 0 1013 2007-10-17 00:13:00.000 A 1 0 0 0 1114 2007-10-17 00:14:00.000 A 1 0 0 0 1215 2007-10-17 00:15:00.000 A 2 0 1 0 13but i was looking for something like this! and setfirst was good escept for the last Prd AID DateValue Prd statusid setfirst setlast setid setsequence1 2007-10-17 00:01:00.000 A 1 1 0 1 12 2007-10-17 00:02:00.000 A 1 0 0 2 13 2007-10-17 00:03:00.000 A 1 0 0 0 14 2007-10-17 00:04:00.000 A 1 0 0 0 15 2007-10-17 00:05:00.000 B 1 1 0 0 16 2007-10-17 00:06:00.000 B 1 0 0 0 17 2007-10-17 00:07:00.000 B 1 0 0 0 18 2007-10-17 00:08:00.000 B 2 0 1 0 19 2007-10-17 00:09:00.000 B 1 1 0 0 210 2007-10-17 00:10:00.000 B 2 0 1 0 211 2007-10-17 00:11:00.000 A 1 1 0 0 312 2007-10-17 00:12:00.000 A 1 0 0 0 313 2007-10-17 00:13:00.000 A 1 0 0 0 314 2007-10-17 00:14:00.000 A 1 0 0 0 315 2007-10-17 00:15:00.000 A 2 0 1 0 3So sequence 1 is one block , Sequence two is another block and Sequence 3 is one more block and after acheiving this i can trap the first and last record of each sequence which is my ultimate goalI guess i am not messing up your labor day weekend!I am really excited and thrilled from your unconditional help for new guy in SQLThanksSimon Fri, 29 Aug 2008 14:44:17 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxI finally had time to test this, and I found something I hadn't noticed before. Your test data (first post) has duplicate IDs and all the dates and times are the same. Is that an accident, or does your real data also have duplicate ID values? Also, the test table has no primary key. Is that true of the real table or just an oversight on the test?In the hope that your data doesn't actually have those problems, I created the following test and code:[code]CREATE TABLE #mytable ( ID INT IDENTITY(1,1) , DateValue DATETIME, Prd nvarchar(250), Statusid INT )INSERT INTO #mytable (DateValue,Prd, Statusid) SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALLSELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALLSELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','B','2' UNION ALL SELECT 'Oct 17 2007 12:00AM','B','1' UNION ALLSELECT 'Oct 17 2007 12:00AM','B','2' UNION ALLSELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','A','1' UNION ALL SELECT 'Oct 17 2007 12:00AM','A','2'update #MyTableset DateValue = dateadd(minute, ID, DateValue)alter table #MyTableadd SetFirst bit not null default(0), SetLast bit not null default(0);with CTE (Row, Date, Prd, StatusID, SetFirst, SetLast) as (select row_number() over (partition by Prd order by DateValue), DateValue, Prd, StatusID, SetFirst, SetLast from #MyTable)update C1set SetFirst = case when C2.row is not null then 1 when not exists (select * from #MyTable mt where mt.prd = c1.prd and mt.datevalue < c1.date) then 1 else 0end,SetLast =case when C3.row is not null then 1 when not exists (select * from #MyTable mt where mt.prd = c1.prd and mt.datevalue > c1.date) then 1 else 0endfrom CTE C1left outer join CTE C2 -- The prior row is a 2 on C1.Prd = C2.Prd and C1.Row = C2.Row+1 and C1.StatusID = 1 and C2.StatusID = 2left outer join CTE C3 -- Status 2 and next row is a 1 on C1.Prd = C3.Prd and C1.Row = C3.Row-1 and C1.StatusID = 2 and C3.StatusID = 1select *from #MyTable[/code]Because of the Not Exists tests, it has at least that much row-by-row. There's almost certainly a better way to do that particular test, but I don't have time to mess around with that right now. This way works, at least on this data.Another thing I noticed about your test data is that you had two StatusID 2 in a row for Prd "B". That seems to indicate a close without an open. I got rid of that for my test, but if it really exists in your database, the code will have to correct for that.Fri, 29 Aug 2008 08:08:30 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]GSquared (8/28/2008)[/b][hr]The first query breaks them up into sets, based on the SetStart and SetEnd columns and the Date column.The second query then takes each set and gives it a sequence number within the set.Is that what you're looking for? Is it clear enough?[/quote]Unfortunately this type of problem is not so clear to most users even in its recognition let alone a solution. Sql server doesn't help matters by offering no construct(s) to directly solve the problem. And what is the clearest, simplest way to characterize a problem like this so it becomes easy to communicate about it? This problem, along with relational division, appears to be sqls version of porn. You can't nail down the definition but you know it when you see it:)These are two pretty good threads about approaching this type of problem. They do contain spoons but forks as well. FWIW I'm dassin, steve.'searching for the longest subsequence of ones'[url=http://tinyurl.com/5g2dgq][b]http://tinyurl.com/5g2dgq[/b][/url]'Roll Up IP-Location Database'[url=http://tinyurl.com/6evlbr][b]http://tinyurl.com/6evlbr[/b][/url][url=http://www.beyondsql.blogspot.com ][b]www.beyondsql.blogspot.com[/b][/url]Thu, 28 Aug 2008 22:59:27 GMTsteve dassinhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]rbarryyoung (8/28/2008)[/b][hr][quote][b]Jeff Moden (8/28/2008)[/b][hr] It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.[/quote]Man, I think that I linked to that article like 20 times today. Now I know why you and Jack put it into your signature![/quote]Heh... yeah... I got tired of looking it up. Sure wish Steve could find a way to make it a "sticky" in some of the forums.Thu, 28 Aug 2008 22:41:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]Jeff Moden (8/28/2008)[/b][hr] It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.[/quote]Man, I think that I linked to that article like 20 times today. Now I know why you and Jack put it into your signature!Thu, 28 Aug 2008 20:27:12 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspx[quote][b]GSquared (8/28/2008)[/b][hr]Jeff, half my code posts aren't tested, because I don't have the data to test them or don't have the time. Trust me, those ones quite regularly have a few errors in them. I think I even had a backwards inequality relationship in one that made it into complete nonsense. (The other half, I have the data and time to test, and those ones work as written.)[/quote]Guess I have to say that, knowing that, I'm even more impressed with your work. :) It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.Thu, 28 Aug 2008 19:21:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiGsquaredSetfirst still missing the tag even after chaning the first query and even SetLast is taging on some place and not in other!ThanksSimonThu, 28 Aug 2008 13:11:46 GMTsimon phoenix-479217http://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxI misunderstood your question about "Row". No, the ID column is the ID from the original table. You'll need to add that to the temp table.Thu, 28 Aug 2008 09:39:44 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxIn the first query, change:and C1.Row = C3.Row+1to:and C1.Row = C3.Row-1See if that gets you what you need.On the second query, I often call the row_number column in a CTE "Row". It's generic for me. Doesn't matter what you call it.Thu, 28 Aug 2008 09:37:51 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic559271-145-1.aspxHiGsquaredYour first query for SetFirst and Set last was tagging the setfirst to the record just before the setlast (stausid=2) but i was looking for it to tag for the very first record it found order by id as setfirst=1 and again setfirst =1 immediately after statusid=2statusid setfirst setlast1 1 01 0 01 0 02 0 11 1 01 0 02 0 1Righ now your query is tagging the setfirst=1 one record before it sees statusid=2, that do not represent the section, section is where it first starts and closedOne more question on your second query, is that id same as column 'Row' from the previous #temptable , if not it do not exist in our previous #temptable to select? Sets1 (ID, SetID1) as (select id,Again , GSquared Tons of thanks for spending your valuable , SQL guru time for my tedious taskThanksSimonThu, 28 Aug 2008 09:28:05 GMTsimon phoenix-479217