Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find sequential numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, April 5, 2014 4:10 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, May 5, 2014 2:17 PM Points: 9, Visits: 18
 Hi, I have a problem. In my databse I have the following numbers available: 101 104 105 110 111 112 113 114 What I need is to get a select query with records and sequentials numbers after it like: 101 0 104 1 (the number 105) 105 0 110 4 (the numbers 111,112,113,114) 111 3 (the numbers 112,113,114) 112 2 (the numbers 113,114) 113 1 (the numbers 114) 114 0 How can I do It?
Post #1558709
 Posted Saturday, April 5, 2014 5:09 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 3:44 AM Points: 156, Visits: 619
 To solve this problem you can use an excellent article (actually series of articles) by Itzik Ben Gan about gaps and islands. Since you are using SQL 2000 you could use the solution from this sample chapter from the book SQL Server MVP Deep Dives. For your problem you should use the code from listing 10 (Listing 10 Islands—solution 4 using cursors) and slightly modify it. Instead of the last line:`SELECT start_range, end_range FROM @Islands;`you have to use:`SELECT t.id, (SELECT MIN(end_range) FROM @Islands i WHERE i.end_range >= t.id) - t.id AS cntFROM dbo.T1 t `In the sample chapter you can also find an explanation why in this case a cursor solution overperforms a set based one. ___________________________Do Not Optimize for Exceptions!
Post #1558713
 Posted Saturday, April 5, 2014 6:41 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 2:18 AM Points: 1,081, Visits: 6,513
 The easiest and fastest way to process your table would be the "quirky update". See Jeff Moden's article here. Post back if you have any questions after reading the article. Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1558726
 Posted Saturday, April 5, 2014 9:36 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 4:02 AM Points: 1,907, Visits: 19,041
 just to be absolutely sure....you are running SQL 7 or SQL 2000....not a later version? ______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day
Post #1558738
 Posted Saturday, April 5, 2014 9:44 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, May 5, 2014 2:17 PM Points: 9, Visits: 18
 Humm, wrong. Shame on me. It is SQL 2008 or 2012Sorry about this.
Post #1558741
 Posted Saturday, April 5, 2014 9:59 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:03 PM Points: 36,983, Visits: 31,508
 milos.radivojevic (4/5/2014)In the sample chapter you can also find an explanation why in this case a cursor solution overperforms a set based one.While that example is technically "set-based" because it produces sets, it's actually a form of hidden RBAR known as a "Triangular Join" because it "touches" each row many more times than it needs to. With the right indexing, it can sometimes be made to run quite fast but the I/O is astronomical and can drive CPUs, I/O, and Duration into the stops. Here's the article that explains why it's "Hidden RBAR" and why it's a bad thing to do in most cases. Although I had written many prior posts using the term "RBAR", this is the first article I wrote that used the term.http://www.sqlservercentral.com/articles/T-SQL/61539/Although a cursor would almost certainly be faster than the "Triangular Join" method, an explicit cursor or While loop is still not the way to go if you actually need performance for this problem... not even in SQL Server 7 or 2000. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1558744
 Posted Saturday, April 5, 2014 10:16 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, May 5, 2014 2:17 PM Points: 9, Visits: 18
 I realy need some performance on it. So, how can I do it.The idea is to use blocks of date with hours. I need to make an appointment, and if it is for an hour, I need to know the free spaces that have 2 rows free. I am working with half hour.
Post #1558745
 Posted Saturday, April 5, 2014 10:26 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 4:02 AM Points: 1,907, Visits: 19,041
 maybe more helpful to all of us (including your goodself) , if you provide some set up scripts for tables and sample data and expected results......that way we can address your actual problem .are you able to do this? ______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day
Post #1558747
 Posted Saturday, April 5, 2014 10:36 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 4:03 PM Points: 36,983, Visits: 31,508