March 26, 2009 at 7:32 am
There is a TestTab1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTab1](
[id] [smallint] NOT NULL,
[starttime] [datetime] NOT NULL,
) ON [PRIMARY]
The data in the table is as follows:
id starttime
1 2008-03-16 16:27:14.000
2 2008-03-29 11:30:32.000
3 2008-03-29 23:05:32.000
4 2008-03-29 23:18:25.000
Now I want it the following way:
id starttime endtime
1 2008-03-16 16:27:14.000 2008-03-29 11:30:32.000
2 2008-03-29 11:30:32.000 2008-03-29 23:05:32.000
3 2008-03-29 23:05:32.000 2008-03-29 23:18:25.000
4 2008-03-29 23:18:25.000
Which means, starttimes will remain the same as they are, but the starttime of the following row will become the endtime of the previous row.
Can someone help me to achieve this please?
March 26, 2009 at 8:24 am
I may be missing something. It doesn't look like you have end time in the table anywhere, so it seems to me that what you mean is that the end time becomes the start time of the next row, as opposed to the start time being changed. Is that correct?
If so, you just have to join the table to itself to get that.
I did some tests on various methods of doing this, and here's what I came up with:
create table #T (
ID int identity primary key,
StartTime datetime not null);
--
insert into #T(StartTime)
select dateadd(minute, number, 0)
from dbo.Numbers;
--
set nocount on;
set statistics time on;
set statistics io on;
--
;with CTE (Row, ID, ST) as
(select row_number() over (order by StartTime),
ID,
StartTime
from #T)
select t1.ID, t1.ST as StartTime, t2.ST as EndTime
from CTE t1
left outer join CTE t2
on t1.Row = t2.Row-1;
/*
SQL Server parse and compile time:
CPU time = 62 ms, elapsed time = 219 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#T'. Scan count 2, logical reads 262, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 690 ms.
*/
select t1.ID, t1.StartTime, min(t2.StartTime) as EndTime
from #T t1
inner join #T t2
on t1.StartTime < t2.StartTime
group by t1.ID, t1.StartTime;
/*
SQL Server parse and compile time:
CPU time = 32 ms, elapsed time = 83 ms.
Table 'Worktable'. Scan count 50001, logical reads 2849679, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#T'. Scan count 2, logical reads 262, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 719250 ms, elapsed time = 725372 ms.
*/
select ID, StartTime,
(select min(StartTime)
from #T t2
where StartTime > t1.StartTime) as EndTime
from #T t1;
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
Table 'Worktable'. Scan count 50001, logical reads 2849679, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table '#T'. Scan count 2, logical reads 262, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 410281 ms, elapsed time = 412961 ms.
*/
The fastest solution, using the CTE and Row_Number is, of course, 2005 and later only. Won't work in SQL 2000. Shouldn't be a problem for you, since I'm assuming you're using 2005 based on the forum you posted in.
As you can see from the posted stats, that one took just over half a second, while the other solutions took several minutes each.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 10:04 am
Blimey Gus, just as a matter of interest (and on a hunch after reading something Gail wrote some time ago) I set this up with a correlated subquery to compare with the other methods. I'm talking to a 2k box so no CTE. Here's the results:
drop table #T
create table #T (
ID int identity primary key,
StartTime datetime not null);
---
insert into #T(StartTime)
select dateadd(minute, number, 0)
from dbo.Numbers where number <= 50000;
---
set nocount on;
set statistics time on;
set statistics io on;
---
select t1.ID, t1.StartTime, min(t2.StartTime) as EndTime
from #T t1
inner join #T t2
on t1.StartTime < t2.StartTime
group by t1.ID, t1.StartTime
order by t1.ID;
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 8084, logical reads 907011, physical reads 0, read-ahead reads 0.
Table '#T__________________________________________________________________________________________________________________00000000D87C'. Scan count 12, logical reads 917, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 8084, logical reads 907011, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 8419, logical reads 942521, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 8469, logical reads 947821, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 8469, logical reads 947821, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 8469, logical reads 947821, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 752389 ms, elapsed time = 127929 ms.
*/
select ID, StartTime,
(select min(StartTime)
from #T t2
where StartTime > t1.StartTime) as EndTime
from #T t1;
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Worktable'. Scan count 50000, logical reads 2849257, physical reads 0, read-ahead reads 0.
Table '#T__________________________________________________________________________________________________________________00000000D87C'. Scan count 2, logical reads 262, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 345281 ms, elapsed time = 347928 ms.
*/
SELECT t.*, (SELECT starttime FROM #T WHERE [id] = (SELECT MIN([id]) FROM #T WHERE [id] > t.[id]))
FROM #T t
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#T__________________________________________________________________________________________________________________00000000D87C'. Scan count 50001, logical reads 100260, physical reads 0, read-ahead reads 72.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 651 ms.
*/
SELECT t.*, (SELECT starttime FROM #T t INNER JOIN (SELECT MIN([id]) AS [id] FROM #T WHERE [id] > t.[id])d ON d.[id] = t.[id])
FROM #T t
/*
SQL Server parse and compile time:
CPU time = 3 ms, elapsed time = 3 ms.
Table '#T__________________________________________________________________________________________________________________00000000D87C'. Scan count 50001, logical reads 100260, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 647 ms.
*/
In real time, results - all of them - returned immediately from the correlated subqueries but took several minutes from the triangular joins. I started with a data set of 100,000 rows but cancelled after 7 or 8 minutes and chopped the rowcount down to 50,000 (100,000 rows would take 4 times as long as 50,000 rows?).
For fast, accurate and documented assistance in answering your questions, please read this article.
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
March 26, 2009 at 11:34 am
If you can safely assume that the next highest ID is also the next date, those methods will work. If that's not necessarily true, you can't count on them. All it takes is someone entering a row out of sequence. "Oh yeah, and we started again at 3 yesterday. Did you get that one in there?"
I tried adding an index on StartDate to the temp table, and that cut the correlated sub-query version down to 515 ms CPU, 630 ms total, 5002 scans, 156457 logical reads. That's on my sub-query, using the Start Time as the criterion.
Same index cut the CTE and Row_Number version down to 16 ms CPU, 518 ms total, 2 scans, 190 logical reads.
In both cases, returning the data to the screen is a significant part of the run-time. The main advantage to the 2005-only version is the massively reduced IO. On a lot of systems, that'll matter more than the reduction in CPU time or the negligible reduction in total run-time. Assuming it's an option.
Even with that index, the Join and Group By method is still quite slow, and has the worst IO of all three options. This time, I killed it at the five minute mark. No reason to let it keep running.
All of these tests were done on a table with 50k rows. Low-end computer (Core 2 Duo), running SQL Express.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 9:47 am
Spot on. I should have known that you would have tried and covered all bases 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
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
March 30, 2009 at 10:09 am
Nah. I do miss stuff. Challenging me is a good thing. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 24, 2020 at 12:30 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply