Search results for query: *

I had checked out that same post on SO and was trying to wrap my head around it while waiting for other possible responses on here. I got sidetracked and still haven't had the time to sit down and go through what it's actually doing. I'm going to come up with several test cases to run through...

Dynamic number of groups

You are correct, values will not show up in multiple groups.

So there's 2 problems I need to overcome to get the recursive CTE to work. 1) The windowing function I used to give an incremental value to each row didn't work as expected. This is probably due to the way CTEs work. Good for performance, bad for me. The ROW_NUMBER windowing function does the...

Nevermind, I got too excited and didn't realize I was only returning 23 rows, not 23 groups. Should be 46 rows with 23 distinct groups. Argh! Feeling a bit recursive... how appropriate

I'm so close. The DENSE_RANK in the recursive member of the CTE is what's incorrect. I was hoping I could partition by a number of rows which is why I included the level. I'm using SS2012 and saw a ROWS clause but it kept throwing error about being used in a windowing function and I'm not sure...

Thanks, that makes me feel better that it's not only me that has a hard time understanding it. Here's the manual, non-vertical method for producing the output CREATE TABLE #temp1 (GroupID INT, MyValue INT) INSERT INTO #temp1 (GroupID, MyValue) VALUES (1,8),(2,7),(2,9),(3,1),(3,6),(3,3)...

I've been working on a problem that I can't quite figure out. I've tried different combinations of cross joins, CTEs, windowing functions, etc but could never quite get there. I'm also not wanting to go the dynamic SQL route. Can someone please help? Given a variable set of grouped values...

We're a small shop and this production box is the only one with an array large enough to handle these configurations so it's really not anything I can play around with. Is your "probably not" based on the fact that the file(s) are all on 1 logical drive or other factors? --Adam --"He who knows...

SQL Server 2008 R2 I have a 90 GB database running on RAID 10 with 8 spindles. I have 398 tables out of which 3 comprise 87% of all data. Here is the breakdown of percentage of total size: Table1 Clustered 24% Table1 NonClustered 22% Table2 Clustered 17% Table2 NonClustered 12% Table3...

I noticed you had a rowid. I tried something like this but it doesn't handle the last seq_num. You'd also have to do some conversion on seq_num as it looks like it isn't int. I haven't looked at it but the link markros provided is probably more elegant and efficient. DECLARE @table TABLE (...

Are there any other fields that identify the order these records are in? Maybe an identity field? How do you know any given INV 12 is actually "under" SEQ_NUM 00 and not 02? --Adam --"He who knows best knows how little he knows." - Thomas Jefferson

Just realized I wrote "unusually" in my previous post. Should have been "usually", ha. The index didn't make a difference since there are so few records in the temp table. --Adam --"He who knows best knows how little he knows." - Thomas Jefferson

The only scan is on a temp table which the main table joins to. The temp table unsually has under 5 rows. The seek on the main table is now 73% and the join to the temp table 27%. The query result set count varies but I would say anywhere between a couple hundred and 15,000 records usually...

Thought some of you might be interested in an update. While I started down the correct path with parameter sniffing, the final issue was a not-so-covering index. I was thrown off by the large variance in execution times because I was not taking into account the buffer cache. Clearing buffer and...

The RECOMPILE query hint is what I had tried and it slowed it down. I think the only thing left in the list that may need a try is OPTIMIZE FOR UNKNOWN although that seems like it would do the same thing as my local variable parameter sniffing fix (hack). I manually updated the statistics and...

SQL Server 2008 R2 I've got a stored procedure that was taking 30s to run. When I ran it in SSMS it would take under 100ms. The 1st thing I thought was parameter sniffing so I created local variables in the SP and assigned my params to them. That brought the time down to around 5s. I then...

Thanks for the feedback but that doesn't give the required results. With the example I stated it would return all OperatorID 1 records as GroupNumber 1 when in fact there would be 2 different GroupNumbers for OperatorID 1 since OperatorID 2 has a group of events in between. --Adam --"He who...

Hi all, I have a table of operator events. OperatorID and EventTime are the important columns. What I want is to return all of the events but to identify groups of consecutive events by operator without cursors or looping. example (simplified): OperatorID EventTime 1 10:00 1...

I added a union into my proc to return the 2 different levels of aggregation in 1 result set --Adam --"He who knows best knows how little he knows." - Thomas Jefferson