April 5, 2014 at 4:10 am
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?
April 5, 2014 at 5:09 am
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!
April 5, 2014 at 6:41 am
The easiest and fastest way to process your table would be the "quirky update". See Jeff Moden's article here[/url]. Post back if you have any questions after reading the article.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 5, 2014 at 9:36 am
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
April 5, 2014 at 9:44 am
Humm, wrong. Shame on me. It is SQL 2008 or 2012
Sorry about this.
April 5, 2014 at 9:59 am
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
Change is inevitable... Change for the better is not.
April 5, 2014 at 10:16 am
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.
April 5, 2014 at 10:26 am
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
April 5, 2014 at 10:36 am
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
Change is inevitable... Change for the better is not.
April 5, 2014 at 10:47 am
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.
April 5, 2014 at 11:19 am
rui_leote (4/5/2014)
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.
Thanks for that but you didn't read the article I directed you to. That's not readily consumable data. You'll get a whole lot more help much more quickly if you post the data in the readily consumable format the article talks about. It'll take you 15 minutes to read the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2014 at 11:24 am
I agree with you, that's why I say that I was not at home. I post it from my phone. But in an hour or 2 I will post the table and data scripts. Sorry
April 5, 2014 at 11:35 am
rui_leote (4/5/2014)
Humm, wrong. Shame on me. It is SQL 2008 or 2012Sorry about this.
some significant changes between these two editions.....which one is in use now?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 5, 2014 at 11:57 am
J Livingston SQL (4/5/2014)
some significant changes between these two editions.....which one is in use now?
Lets do both with the same solution
😎
/* SAMPLE DATA */
SELECT
X.NUM
INTO #MY_NUMBERS
FROM
(
SELECT 101 AS NUM UNION ALL
SELECT 104 AS NUM UNION ALL
SELECT 105 AS NUM UNION ALL
SELECT 110 AS NUM UNION ALL
SELECT 111 AS NUM UNION ALL
SELECT 112 AS NUM UNION ALL
SELECT 113 AS NUM UNION ALL
SELECT 114 AS NUM
) AS X;
/* Group the entries using the number - row_number, which
is the same within each group.
*/
;WITH NUM_GROUP AS
(
SELECT
NM.NUM
,NM.NUM - ROW_NUMBER() OVER (ORDER BY NM.NUM) AS GR_NO
FROM #MY_NUMBERS NM
)
/* */
SELECT
NG.NUM
,LAST_VALUE(NG.NUM) OVER
(
PARTITION BY NG.GR_NO
ORDER BY NG.NUM
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) - NG.NUM AS NUM_SLOTS
FROM NUM_GROUP NG
/* CLEAN UP */
DROP TABLE #MY_NUMBERS;
SELECT Eventdate,Status
INTO #AP_DATA
FROM (VALUES
('2014-01-20 18:00', 0)
,('2014-01-20 18:30', 0)
,('2014-01-20 19:00', 1)
,('2014-01-20 19:30', 0)
,('2014-01-20 20:00', 1)
,('2014-01-20 20:30', 0)
,('2014-01-20 21:00', 0)
,('2014-01-20 21:30', 0)
,('2014-01-20 22:00', 0)
,('2014-01-20 22:30', 1)
,('2014-01-20 23:00', 0)
) AS X(Eventdate,Status);
;WITH GROUP_PART AS
(
SELECT
CONVERT(DATETIME2(0),Eventdate,120) AS Eventdate
,(DATEDIFF(MINUTE,'1900-01-01 00:00',Eventdate) / 30) - ROW_NUMBER() OVER (ORDER BY AD.Eventdate) AS GR_NO
FROM #AP_DATA AD
WHERE AD.Status = 0
)
SELECT
GP.Eventdate
,(DATEDIFF(MINUTE,GP.Eventdate,LAST_VALUE(GP.Eventdate) OVER
(
PARTITION BY GP.GR_NO
ORDER BY GP.Eventdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)) / 30 ) + 1 AS AP_LAST
FROM GROUP_PART GP;
DROP TABLE #AP_DATA;
Edit: creeping requirements, added 1 to the output (AP_LAST)
April 5, 2014 at 12:24 pm
Eirikur Eiriksson (4/5/2014)
J Livingston SQL (4/5/2014)
some significant changes between these two editions.....which one is in use now?Lets do both with the same solution
😎
/* SAMPLE DATA */SELECT
X.NUM
INTO #MY_NUMBERS
FROM
(
SELECT 101 AS NUM UNION ALL
SELECT 104 AS NUM UNION ALL
SELECT 105 AS NUM UNION ALL
SELECT 110 AS NUM UNION ALL
SELECT 111 AS NUM UNION ALL
SELECT 112 AS NUM UNION ALL
SELECT 113 AS NUM UNION ALL
SELECT 114 AS NUM
) AS X;
/* Group the entries using the number - row_number, which
is the same within each group.
*/
;WITH NUM_GROUP AS
(
SELECT
NM.NUM
,NM.NUM - ROW_NUMBER() OVER (ORDER BY NM.NUM) AS GR_NO
FROM #MY_NUMBERS NM
)
/* */
SELECT
NG.NUM
,LAST_VALUE(NG.NUM) OVER
(
PARTITION BY NG.GR_NO
ORDER BY NG.NUM
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) - NG.NUM AS NUM_SLOTS
FROM NUM_GROUP NG
/* CLEAN UP */
DROP TABLE #MY_NUMBERS;
SELECT Eventdate,StatusINTO #AP_DATA
FROM (VALUES
('2014-01-20 18:00', 0)
,('2014-01-20 18:30', 0)
,('2014-01-20 19:00', 1)
,('2014-01-20 19:30', 0)
,('2014-01-20 20:00', 1)
,('2014-01-20 20:30', 0)
,('2014-01-20 21:00', 0)
,('2014-01-20 21:30', 0)
,('2014-01-20 22:00', 0)
,('2014-01-20 22:30', 1)
,('2014-01-20 23:00', 0)
) AS X(Eventdate,Status);
;WITH GROUP_PART AS
(
SELECT
CONVERT(DATETIME2(0),Eventdate,120) AS Eventdate
,(DATEDIFF(MINUTE,'1900-01-01 00:00',Eventdate) / 30) - ROW_NUMBER() OVER (ORDER BY AD.Eventdate) AS GR_NO
FROM #AP_DATA AD
WHERE AD.Status = 0
)
SELECT
GP.Eventdate
,(DATEDIFF(MINUTE,GP.Eventdate,LAST_VALUE(GP.Eventdate) OVER
(
PARTITION BY GP.GR_NO
ORDER BY GP.Eventdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)) / 30 )AS AP_LAST
FROM GROUP_PART GP;
DROP TABLE #AP_DATA;
are these just for 2012?
I "thought" that UNBOUNDED PRECEDING/FOLLOWING was introduced in 2012...so not available in 2008?
may well be wrong.
nevertheless it provide a solution...
I wonder what is really required....me thinks we are only seeing part of a bigger problem in providing end data to an app.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply