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 1234»»»

Sequence-numbering groups Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 3:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 26, Visits: 223
I have a table of checks, each of which has a Status of 'Open' or 'Void'. Below are some sample data.

CKID Stat
101 Open
102 Open
103 Open
104 Void
105 Void
106 Open
107 Open
108 Open
109 Void
110 Open

I would like to create a CTE that would include a column for each group of checks, as shown below.

CKID Stat Grp
101 Open 1
102 Open 1
103 Open 1
104 Void 2
105 Void 2
106 Open 3
107 Open 3
108 Open 3
109 Void 4
110 Open 5

Thank you for any help.
Post #1008083
Posted Wednesday, October 20, 2010 4:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:51 AM
Points: 5,446, Visits: 7,616
A serial update might work for this, but the implementation would require me to see the underlying DDL for the table and indexing, specifically the clustered index.

For the proper rules to doing something like this, do a search for 'Quirky Update' here on SSC and you'll find a very detailed article on how to do something like that.

EDIT: found it: http://www.sqlservercentral.com/articles/T-SQL/68467/


Other than that, you'll have to make data-islands of the data and then perhaps setup a row_number() over on the subset. I'll have to chew on that one a while.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1008094
Posted Wednesday, October 20, 2010 4:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
It's slightly more complicated than just using a CTE...

DECLARE @tbl TABLE
(
ckid INT,stat VARCHAR(10)
)
INSERT INTO @tbl
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open'

SELECT *
FROM @tbl

;WITH cte AS -- row number per group
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY ckid) r1,
ROW_NUMBER() OVER(ORDER BY ckid)-
ROW_NUMBER() OVER(PARTITION BY stat ORDER BY ckid ) AS ROW
FROM @tbl
),cte2 AS -- row number per group range, ordered by ckid
(
SELECT MIN(r1) mi,MAX(r1) ma,stat,ROW, ROW_NUMBER() OVER(ORDER BY MIN(r1) ) r2
FROM cte
GROUP BY stat,ROW
)
SELECT ckid ,cte.stat,cte2.r2
FROM cte
INNER JOIN cte2 ON r1>=mi AND r1<=ma
ORDER BY r1





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1008099
Posted Wednesday, October 20, 2010 4:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 26, Visits: 223
Thank you both for your help, Craig for the interesting article which I have started reading, and Lutz for the solution I needed.

gmrose
Post #1008100
Posted Wednesday, October 20, 2010 5:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:51 AM
Points: 5,446, Visits: 7,616
Lutz, that is wild. I like it. Thanks.

Have you done that on huge recordsets? Does it perform well?



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1008108
Posted Wednesday, October 20, 2010 6:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
Craig Farrell (10/20/2010)
Lutz, that is wild. I like it. Thanks.

Have you done that on huge recordsets? Does it perform well?


No, I haven't. And I probably won't either.
Such requirements and some rather large tables actually call for the quirky update from my point of view.
I don't expect the CTE to perform anywhere near the quirky update. Mainly because of the triple sort operation, the aggregation and the join on a range. But it should outperform any loop.

So, why did I posted this solution anyway?
#1: You already posted the link to Jeffs article.
#2: I did pretty much what you've already described. Just using T-SQL over English
#3: gmrose specifically asked for a cte solution and
#4: it was some kind of an exercise for me to do it using a non-quirky-update method.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1008111
Posted Wednesday, October 20, 2010 6:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 26, Visits: 223
After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.

Thanks anyways.

gmrose
Post #1008114
Posted Wednesday, October 20, 2010 9:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 5,367, Visits: 8,989
Looks like it's time for a quirky update solution:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
-- This primary key is crucial. If your table doesn't have a clustered index
-- on this, then you will have to dump the data to a temp table (along with
-- the PK columns of the table), use this PK, then update the real table
-- by joining this temp table back to the real table by the PK columns.
CREATE TABLE #temp (ckid INT PRIMARY KEY CLUSTERED, stat varchar(10), Grp int);
INSERT INTO #temp (ckid, stat)
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open';

-- declare and initialize variables needed in the update statement.
DECLARE @Sequence int, -- for safety check
@stat varchar(10), -- to hold stat column from last row
@grp int, -- current grp number
@ckid int; -- for anchor column
SET @Sequence = 1;
SET @grp = 1;

/*
This form of the UPDATE statement has some rules for proper usage.
See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
for a complete discussion of how this works, and all of the rules for utilizing it.
If you don't follow ALL the rules, you WILL mess up your data.
*/
WITH SafeTable AS
(
-- generate table with a sequence column in clustered index order
-- in order to verify that update is happening in the correct order
SELECT ckid,
stat,
grp,
Sequence = ROW_NUMBER() OVER (ORDER BY ckid)
FROM #temp
)
UPDATE t
-- verify in proper sequence order; if not, throw an error so nothing is updated
SET @grp = grp = CASE WHEN Sequence = @Sequence THEN
CASE WHEN stat <> @Stat THEN @grp + 1
-- if you have to separate this by account numbers also, then you will need
-- a when clause to handle it here also.
-- different stat --> increment grp number
ELSE @grp END -- same stat --> same grp number
ELSE 1/0 END, -- not in proper sequence order, so throw an error
@Sequence = @Sequence + 1,
@stat = stat, -- get the current value to compare to in next row
@ckid = ckid -- anchor column
FROM SafeTable t WITH (TABLOCKX) -- lock table
OPTION (MAXDOP 1); -- prevent parallelism!

select * from #temp;



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1008146
Posted Wednesday, October 20, 2010 10:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:22 PM
Points: 2,262, Visits: 5,421
Wayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )
What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?

P.S: i am still learning the QU, so please pardon my ignorance..
Post #1008167
Posted Thursday, October 21, 2010 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
gmrose (10/20/2010)
After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.

Thanks anyways.

gmrose


How many rows does your table have? And what indexes?
Please post complete DDL for the table in question.
Edit: the actual execution plan would be great, too...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1008557
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse