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

Find sequential numbers Expand / Collapse
Author
Message
Posted Saturday, April 5, 2014 4:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 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 cnt
FROM 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 1,090, Visits: 6,551
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 1,913, Visits: 19,143
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 2012
Sorry about this.
Post #1558741
Posted Saturday, April 5, 2014 9:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 36,995, Visits: 31,520
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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558744
Posted Saturday, April 5, 2014 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 1,913, Visits: 19,143
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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 36,995, Visits: 31,520
rui_leote (4/5/2014)
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.


Your original post said nothing about dates and hours and any solution based on your original post will still need a lot of work to do what you want.

As JLS suggested above, we need you to help us help you. Please see the article at the first link under "Helpful Links" in my signature line below and I'm absolutely positive that someone will be able to write some very high performance code for you based on the readily consumable data that you post according to the article I've cited. It should only take you several minutes once you've read the article and will save you and us a huge amount of wasted time trying to explain.


--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 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558748
Posted Saturday, April 5, 2014 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 2:17 PM
Points: 9, Visits: 18
I can do it, I am not at home now. I have a table with only 2 columns. Let's say "eventdate" and "status".
Eventdate is datetime
Status is int.

I have records with dates, let's say:
Eventdate. Status
20-01-2014 18:00 0
20-01-2014 18:30 0
20-01-2014 19:00 1
20-01-2014 19:30 0
20-01-2014 20:00 1
20-01-2014 20:30 0
20-01-2014 21:00 0
20-01-2014 21:30 0
20-01-2014 22:00 0
20-01-2014 22:30 1
20-01-2014 23:00 0

I need to performe a select query that gives me the dates available
Status=0 and the number of sequential dates available.
Like this
Event date. Number sequentials
20-01-2014 18:00 2 (this record plus 18:30)
20-01-2014 18:30 1 (this record)

20-01-2014 19:30 1 (this record)

20-01-2014 20:30 4 (20:30, 21:00, 21:30 and 22:00)
20-01-2014 21:00 3 (21:00, 21:30 and 22:00)
20-01-2014 21:30 2 (21:30 and 22:00)
20-01-2014 22:00 1 (22:00)

20-01-2014 23:00 1 (this record )


This is what I want, if I need to make an hour appointment I just search a date with 2 or more sequentials.
Post #1558750
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse