August 30, 2008 at 9:38 pm
Hi
Jeff
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:)
Thanks
Simon
August 30, 2008 at 11:43 pm
--===== 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
--Jeff Moden
August 31, 2008 at 12:04 am
Hi
Jeff
Everything 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:) GBY
Thanks
Simon
August 31, 2008 at 9:14 am
simon phoenix (8/31/2008)
HiJeff
Everything 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:) GBY
Thanks
Simon
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. 😀
I 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 http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/ . The problem that most will talk about starts in the section called "Gail's "Merry-go-Round" Index". Basically, every one has been "brought up" to believe that the only way you can guarantee the "processing order" 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 "Stop the Merry-go-Round, I wanna get off! (Final Test Data)"... 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.
--Jeff Moden
August 31, 2008 at 4:10 pm
Jeff Moden (8/30/2008)
--===== Show first and last PRD and date of each SetSequenceSELECT 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
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):)
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~'
ID DateValue Prd Statusid Setsequence Setfirst Setlast
---- ------------------- ---- -------- ----------- ----------- -----------
1 Oct 17 2007 12:01AM A 1 1 1 0
2 Oct 17 2007 12:02AM A 1 1 0 0
3 Oct 17 2007 12:03AM A 1 1 0 0
4 Oct 17 2007 12:04AM A 1 1 0 0
5 Oct 17 2007 12:05AM B 1 1 0 0
6 Oct 17 2007 12:06AM B 1 1 0 0
7 Oct 17 2007 12:07AM B 1 1 0 0
8 Oct 17 2007 12:08AM B 2 1 0 1
9 Oct 17 2007 12:09AM B 1 2 1 0
10 Oct 17 2007 12:10AM B 2 2 0 1
11 Oct 17 2007 12:11AM A 1 3 1 0
12 Oct 17 2007 12:12AM A 1 3 0 0
13 Oct 17 2007 12:13AM A 1 3 0 0
14 Oct 17 2007 12:14AM A 1 3 0 0
15 Oct 17 2007 12:15AM A 2 3 0 1
Jeff Moden (8/30/2008)
Now, the clustered index makes it easy using the very same technique in the following article...http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
--===== 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
Questions? 🙂
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.programming
Mar 7 2003
"transpose problem"
(Especially the replies by Bill Hollinshead [MSFT].)
microsoft.public.sqlserver.programming
Jan 23 2003
"Undocumented operation"
(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"
http://beyondsql.blogspot.com/2008/04/sql-ranking-overture.html
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]
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!) 🙂
September 1, 2008 at 10:58 am
Heh...
I appreciate the advertised "simplicity" of your product, but don't try to justify it by posting a bunch of bloated links with bad information in them. 😉
Both the UPDATE method of SET @variable = column = expression and SELECT (or SET) @variable = @variable op expression ARE 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...
UPDATE
Changes existing data in a table.
Syntax
UPDATE {
table_name WITH ( [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]
And, now, on the subject of SELECT @variable = @variable or SELECT @variable = @variable op expression... again, from Books Online...
SELECT @local_variable
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.
Syntax
SELECT { @local_variable = expression } [ ,...n ]
Arguments
@local_variable
Is a declared variable for which a value is to be assigned.
expression
Is any valid Microsoft® SQL Server™ expression, including a scalar subquery.
... and on the subject of SET @variable = @variable or SET @variable = @variable op expression... again, from Books Online...
SET @local_variable
Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value.
Syntax
SET { { @local_variable = expression }
| { @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_variable
Is 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.
expression
Is any valid Microsoft® SQL Server™ expression.
Further... (also from books online)...
Expressions
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.
Syntax
{ constant
| scalar_function
| [ alias. ] column
| local_variable
| ( expression )
| ( scalar_subquery )
| { unary_operator } expression
| expression { binary_operator } expression
}
Arguments...
...
...
(expression)
Is any valid SQL Server expression as defined in this topic. 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.
The operation of a clustered index IS 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 some expect 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 displayed, but not as to the order they are processed.
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.
--Jeff Moden
September 2, 2008 at 10:43 am
Hi
Jeff
In 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
Srid ID DateValue Prd Statusid SetSequence
78 1 01:00.0 A 1 1
78 2 02:00.0 A 1 1
78 3 03:00.0 A 1 1
78 4 04:00.0 A 1 1
78 5 05:00.0 B 1 1
78 6 06:00.0 B 1 1
78 7 07:00.0 B 1 1
78 8 08:00.0 B 2 1
78 9 09:00.0 B 1 2
78 10 10:00.0 B 2 2
78 11 11:00.0 B 2 2
78 12 12:00.0 A 1 3
78 13 13:00.0 A 1 3
78 14 14:00.0 A 1 3
78 15 15:00.0 A 1 3
78 16 16:00.0 A 2 3
79 1 01:00.0 A 1 4
79 2 02:00.0 A 1 4
79 3 03:00.0 A 1 4
79 4 04:00.0 A 1 4
79 5 05:00.0 B 1 4
79 6 06:00.0 B 1 4
79 7 07:00.0 B 1 4
79 8 08:00.0 B 2 4
79 9 09:00.0 B 1 5
79 10 10:00.0 B 2 5
79 11 11:00.0 B 2 5
79 12 12:00.0 A 1 5
79 13 13:00.0 A 1 5
79 14 14:00.0 A 1 5
79 15 15:00.0 A 1 5
79 16 16:00.0 A 2 5
Thanks
Simon
September 2, 2008 at 8:28 pm
Heh... 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...
--Jeff Moden
September 3, 2008 at 8:49 am
The 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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2008 at 5:03 pm
Hi
Jeff, Gus
I 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!
Thanks
Simon
September 3, 2008 at 5:16 pm
Heh... 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?
--Jeff Moden
September 4, 2008 at 4:16 am
simon phoenix (9/2/2008)
HiJeff
In 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
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.
create table mytable2(Srid int,ID int,DateValue DateTime,Prd char(1),Statusid int,
primary key(Srid,ID))
go
insert into mytable2
SELECT 78, 1 , 'Jan 1 1900 1:00AM', 'A', 1 UNION ALL
SELECT 78, 2 , 'Jan 1 1900 2:00AM', 'A', 1 UNION ALL
SELECT 78, 3 , 'Jan 1 1900 3:00AM', 'A', 1 UNION ALL
SELECT 78, 4 , 'Jan 1 1900 4:00AM', 'A', 1 UNION ALL
SELECT 78, 5 , 'Jan 1 1900 5:00AM', 'B', 1 UNION ALL
SELECT 78, 6 , 'Jan 1 1900 6:00AM', 'B', 1 UNION ALL
SELECT 78, 7 , 'Jan 1 1900 7:00AM', 'B', 1 UNION ALL
SELECT 78, 8 , 'Jan 1 1900 8:00AM', 'B', 2 UNION ALL
SELECT 78, 9 , 'Jan 1 1900 9:00AM', 'B', 1 UNION ALL
SELECT 78, 10, 'Jan 1 1900 10:00AM', 'B', 2 UNION ALL
SELECT 78, 11, 'Jan 1 1900 11:00AM', 'B', 2 UNION ALL
SELECT 78, 12, 'Jan 1 1900 12:00PM', 'A', 1 UNION ALL
SELECT 78, 13, 'Jan 1 1900 1:00PM', 'A', 1 UNION ALL
SELECT 78, 14, 'Jan 1 1900 2:00PM', 'A', 1 UNION ALL
SELECT 78, 15, 'Jan 1 1900 3:00PM', 'A', 1 UNION ALL
SELECT 78, 16, 'Jan 1 1900 4:00PM', 'A', 2 UNION ALL
SELECT 79, 1 , 'Jan 1 1900 1:00AM', 'A', 1 UNION ALL
SELECT 79, 2 , 'Jan 1 1900 2:00AM', 'A', 1 UNION ALL
SELECT 79, 3 , 'Jan 1 1900 3:00AM', 'A', 1 UNION ALL
SELECT 79, 4 , 'Jan 1 1900 4:00AM', 'A', 1 UNION ALL
SELECT 79, 5 , 'Jan 1 1900 5:00AM', 'B', 1 UNION ALL
SELECT 79, 6 , 'Jan 1 1900 6:00AM', 'B', 1 UNION ALL
SELECT 79, 7 , 'Jan 1 1900 7:00AM', 'B', 1 UNION ALL
SELECT 79, 8 , 'Jan 1 1900 8:00AM', 'B', 2 UNION ALL
SELECT 79, 9 , 'Jan 1 1900 9:00AM', 'B', 1 UNION ALL
SELECT 79, 10, 'Jan 1 1900 10:00AM', 'B', 2 UNION ALL
SELECT 79, 11, 'Jan 1 1900 11:00AM', 'B', 2 UNION ALL
SELECT 79, 12, 'Jan 1 1900 12:00PM', 'A', 1 UNION ALL
SELECT 79, 13, 'Jan 1 1900 1:00PM', 'A', 1 UNION ALL
SELECT 79, 14, 'Jan 1 1900 2:00PM', 'A', 1 UNION ALL
SELECT 79, 15, 'Jan 1 1900 3:00PM', 'A', 1 UNION ALL
SELECT 79, 16, 'Jan 1 1900 4:00PM', 'A', 2
Sql solution. Similar to my previous one with addition of Srid.
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 Setlast
FROM E
ORDER BY E.Srid,E.ID
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 0
78 2 Jan 1 1900 2:00AM A 1 1 0 0
78 3 Jan 1 1900 3:00AM A 1 1 0 0
78 4 Jan 1 1900 4:00AM A 1 1 0 0
78 5 Jan 1 1900 5:00AM B 1 1 0 0
78 6 Jan 1 1900 6:00AM B 1 1 0 0
78 7 Jan 1 1900 7:00AM B 1 1 0 0
78 8 Jan 1 1900 8:00AM B 2 1 0 1
78 9 Jan 1 1900 9:00AM B 1 2 1 0
78 10 Jan 1 1900 10:00AM B 2 2 0 1
78 11 Jan 1 1900 11:00AM B 2 3 1 1
78 12 Jan 1 1900 12:00PM A 1 4 1 0
78 13 Jan 1 1900 1:00PM A 1 4 0 0
78 14 Jan 1 1900 2:00PM A 1 4 0 0
78 15 Jan 1 1900 3:00PM A 1 4 0 0
78 16 Jan 1 1900 4:00PM A 2 4 0 1
79 1 Jan 1 1900 1:00AM A 1 1 1 0
79 2 Jan 1 1900 2:00AM A 1 1 0 0
79 3 Jan 1 1900 3:00AM A 1 1 0 0
79 4 Jan 1 1900 4:00AM A 1 1 0 0
79 5 Jan 1 1900 5:00AM B 1 1 0 0
79 6 Jan 1 1900 6:00AM B 1 1 0 0
79 7 Jan 1 1900 7:00AM B 1 1 0 0
79 8 Jan 1 1900 8:00AM B 2 1 0 1
79 9 Jan 1 1900 9:00AM B 1 2 1 0
79 10 Jan 1 1900 10:00AM B 2 2 0 1
79 11 Jan 1 1900 11:00AM B 2 3 1 1
79 12 Jan 1 1900 12:00PM A 1 4 1 0
79 13 Jan 1 1900 1:00PM A 1 4 0 0
79 14 Jan 1 1900 2:00PM A 1 4 0 0
79 15 Jan 1 1900 3:00PM A 1 4 0 0
79 16 Jan 1 1900 4:00PM A 2 4 0 1
RAC 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.
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'
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 0
78 2 Jan 1 1900 2:00AM A 1 1 0 0
78 3 Jan 1 1900 3:00AM A 1 1 0 0
78 4 Jan 1 1900 4:00AM A 1 1 0 0
78 5 Jan 1 1900 5:00AM B 1 1 0 0
78 6 Jan 1 1900 6:00AM B 1 1 0 0
78 7 Jan 1 1900 7:00AM B 1 1 0 0
78 8 Jan 1 1900 8:00AM B 2 1 0 1
78 9 Jan 1 1900 9:00AM B 1 2 1 0
78 10 Jan 1 1900 10:00AM B 2 2 0 1
78 11 Jan 1 1900 11:00AM B 2 3 1 1
78 12 Jan 1 1900 12:00PM A 1 4 1 0
78 13 Jan 1 1900 1:00PM A 1 4 0 0
78 14 Jan 1 1900 2:00PM A 1 4 0 0
78 15 Jan 1 1900 3:00PM A 1 4 0 0
78 16 Jan 1 1900 4:00PM A 2 4 0 1
79 1 Jan 1 1900 1:00AM A 1 5 1 0
79 2 Jan 1 1900 2:00AM A 1 5 0 0
79 3 Jan 1 1900 3:00AM A 1 5 0 0
79 4 Jan 1 1900 4:00AM A 1 5 0 0
79 5 Jan 1 1900 5:00AM B 1 5 0 0
79 6 Jan 1 1900 6:00AM B 1 5 0 0
79 7 Jan 1 1900 7:00AM B 1 5 0 0
79 8 Jan 1 1900 8:00AM B 2 5 0 1
79 9 Jan 1 1900 9:00AM B 1 6 1 0
79 10 Jan 1 1900 10:00AM B 2 6 0 1
79 11 Jan 1 1900 11:00AM B 2 7 1 1
79 12 Jan 1 1900 12:00PM A 1 8 1 0
79 13 Jan 1 1900 1:00PM A 1 8 0 0
79 14 Jan 1 1900 2:00PM A 1 8 0 0
79 15 Jan 1 1900 3:00PM A 1 8 0 0
79 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:)
September 4, 2008 at 2:33 pm
Hi
Jeff
i 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 logic
DECLARE @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.Missroute
I 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 guys
Thanks
Simon
September 4, 2008 at 2:39 pm
Hi
Rog
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 lot
Thanks
Simon
September 5, 2008 at 9:03 am
Trust 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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply