Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Variation of streaks and runs (I think)

Author  Topic  Jimbo2 Starting Member 14 Posts Posted - 2004-05-05 : 14:22:46 In the following rows, the page and line need to be filled in. Each page has 3 lines, but there will be a page break if the t-code or the d-code changes! DECLARE @table table (the_key char(3), t_code smallint, d_code smallint, page smallint, line smallint) INSERT INTO @table (the_key, t_code, d_code) SELECT 'aaa', 0, null UNION ALL SELECT 'bbb', 0, null UNION ALL SELECT 'ccc', 0, null UNION ALL SELECT 'ddd', 0, null UNION ALL SELECT 'eee', 1, 123 UNION ALL SELECT 'fff', 1, 123 UNION ALL SELECT 'ggg', 1, 234 UNION ALL SELECT 'hhh', 1, 234 UNION ALL ect... (this table will be in order by t_code, d_code) Would like to see a result like this the_key, t_code, d_code, page, line 'aaa', 0, null, 1, 1 'bbb', 0, null, 1, 2 'ccc', 0, null, 1, 3 'ddd', 0, null, 2, 1 (go to next page after 3 lines) 'eee', 1, 123, 3, 1 (page 3 because t_code changed) 'fff', 1, 123, 3, 2 'ggg', 1, 234, 4, 1 (page 4 because d_code changed) 'hhh', 1, 234, 4, 2 I have read the streaks and runs article written by Jeff Smith and feel this is a variation of that idea, but flipping the page after 3 lines has me stumped. Fwiw, my real-life problem has another little twist I did not include here, there is an additonal column called c_code such that some c_code values have 3 lines per page and some have 5 lines! But I'll wait and tackle that next after I've had a chance to digest this. Any suggestions here would be helpful.