August 10, 2010 at 11:12 am
Hi,
I have a table records like:
5:30 AM11:00 AM
11:30 AM2:30 PM
2:30 PM5:30 PM
5:30 PM11:00 PM
When end time matches with coming start time then the one min will add in start time.
So query should return output like below:
5:30 AM11:00 AM
11:30 AM2:30 PM
2:31 PM5:30 PM
5:31 PM11:00 PM
How to acheive this?
August 10, 2010 at 11:22 am
You would need to use ROW_NUMBER() to put your data in sequence and do a self join on this subquery (or better, CTE) based on the row number column with an offset of 1.
Then you could compare the two values and modify as per your need using a CASE statement.
Side note: I didn't bother to provide a coded answer since there is no table definition nor any ready to use sample data. Especially any information regarding the data type is missing. I truly hope, you're not storing date or time information as character values as it seems to be...
If so, my first advice is: get your table design right and use the proper data type!
August 10, 2010 at 11:24 am
Sagar, try this:
declare @tab table
(
rid int identity(1,1),
starttime datetime,
endtime datetime
)
insert into @tab
select '2010-08-10 5:30 AM' , '2010-08-10 11:00 AM'
union all select '2010-08-10 11:30 AM', '2010-08-10 2:30 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 5:30 PM'
union all select '2010-08-10 5:30 PM' , '2010-08-10 11:00 PM'
select *
from@tab t1
-- Update statement
update t1
set t1.starttime = dateadd(mi,1,t1.starttime)
-- select *
from@tab t1
cross join@tab t2
where t1.starttime = t2.endtime
select *
from@tab t1
August 10, 2010 at 11:27 am
If you dont have an ID column, then as Lutz suggested, use ROW_NUMBER as given below.
declare @tab table
(
rid int identity(1,1),
starttime datetime,
endtime datetime
)
insert into @tab
select '2010-08-10 5:30 AM' , '2010-08-10 11:00 AM'
union all select '2010-08-10 11:30 AM', '2010-08-10 2:30 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 5:30 PM'
union all select '2010-08-10 5:30 PM' , '2010-08-10 11:00 PM'
select *
from@tab t1 ;
-- Update statement
with cte as
(
-- Build a ID for each row on the fly ; this is what Lutz was saying about
select row_number() over(order by (select null)) rn ,
starttime, endtime
from @tab
)
update t1
set t1.starttime = dateadd(mi,1,t1.starttime)
-- select *
fromcte t1
cross joincte t2
where t1.starttime = t2.endtime
select *
from@tab t1
And take a closer look at how i have cooked up the data and posted a ready-to-use environment for coding as well as testing.. to learn how to present your question, please head straight to the first link in Lutz's signature...
August 10, 2010 at 11:29 am
And i had another post form another dude asking exactly the same question... i dont remember that post.. probably u can find more explanations and code in that as well...
August 10, 2010 at 11:41 am
Thanks my friend. Really nice.
August 10, 2010 at 12:46 pm
ColdCoffee (8/10/2010)
If you dont have an ID column, then as Lutz suggested, use ROW_NUMBER as given below.
declare @tab table
(
rid int identity(1,1),
starttime datetime,
endtime datetime
)
insert into @tab
select '2010-08-10 5:30 AM' , '2010-08-10 11:00 AM'
union all select '2010-08-10 11:30 AM', '2010-08-10 2:30 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 5:30 PM'
union all select '2010-08-10 5:30 PM' , '2010-08-10 11:00 PM'
select *
from@tab t1 ;
-- Update statement
with cte as
(
-- Build a ID for each row on the fly ; this is what Lutz was saying about
select row_number() over(order by (select null)) rn ,
starttime, endtime
from @tab
)
update t1
set t1.starttime = dateadd(mi,1,t1.starttime)
-- select *
fromcte t1
cross joincte t2
where t1.starttime = t2.endtime
select *
from@tab t1
And take a closer look at how i have cooked up the data and posted a ready-to-use environment for coding as well as testing.. to learn how to present your question, please head straight to the first link in Lutz's signature...
You're not using your row number anywhere in your subsequent query, so why bother calculating it in the first place? And, since you don't need to calculate the row number, you don't need the CTE.
Also, why did you code this as a CROSS JOIN rather than an inner join?
update t1
set t1.starttime = dateadd(mi,1,t1.starttime)
from@tab t1
INNER join@tab t2
ON t1.starttime = t2.endtime
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 10, 2010 at 1:06 pm
Drew,
I guess the Row_Number approach I was hitting for was overdesigned for the task...
You're absolutely right, for the given task a simple join on the time columns would be enough.
That's why I usually insist in getting ready to use sample data...
One thing the OP should be aware of though: after the first update it's possible to end up with rows where starttime = endtime. But that's a different story.
Sorry for sending you in the wrong direction!
August 10, 2010 at 10:58 pm
drew.allen (8/10/2010)
You're not using your row number anywhere in your subsequent query, so why bother calculating it in the first place? And, since you don't need to calculate the row number, you don't need the CTE.Also, why did you code this as a CROSS JOIN rather than an inner join?
update t1set t1.starttime = dateadd(mi,1,t1.starttime)
from@tab t1
INNER join@tab t2
ON t1.starttime = t2.endtime
Drew
THe OP's request is to find match a row's enddatetime with the very next row's startdatetime and if it matches, increment the next row's startdatetime by 1 minute..That's why i used ROW_NUMBER and CROSS JOIN... Why i dint use it the last query, is because the OP's sample data dint need it.. and i was in a hurry as well..
INNER JOIN will fail with the following sample data :
declare @tab table
(
rid int identity(1,1),
starttime datetime,
endtime datetime
)
insert into @tab
select '2010-08-10 5:30 AM' , '2010-08-10 11:00 AM'
union all select '2010-08-10 11:30 AM', '2010-08-10 2:30 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 5:30 PM'
union all select '2010-08-10 5:30 PM' , '2010-08-10 11:00 PM'
union all select '2010-08-10 1:30 PM' , '2010-08-10 1:40 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 2:50 PM'
where there are 2 matching startdatetime for one enddatteime and those 2 matching rows are placed at different locations in the table. This is where ROW_NUMBER helps.. If u take a closer look, my first query, i introduced an IDENTITY Column to get the row numbers.. but i never used...;
Now for the query that will work for the current above set:
declare @tab table
(
rid int identity(1,1),
starttime datetime,
endtime datetime
)
insert into @tab
select '2010-08-10 5:30 AM' , '2010-08-10 11:00 AM'
union all select '2010-08-10 11:30 AM', '2010-08-10 2:30 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 5:30 PM'
union all select '2010-08-10 5:30 PM' , '2010-08-10 11:00 PM'
union all select '2010-08-10 1:30 PM' , '2010-08-10 1:40 PM'
union all select '2010-08-10 2:30 PM' , '2010-08-10 2:50 PM'
-- Update statement
;with cte as
(
-- Build a ID for each row on the fly ; this is what Lutz was saying about
select row_number() over(order by (select null)) rn ,
starttime, endtime
from @tab
)
update t1
set t1.starttime = dateadd(mi,1,t1.starttime)
--select *
from cte t1
cross join cte t2
where (
t1.starttime = t2.endtime AND
t1.RN = t2.RN + 1
)
select *
from @tab t1
Please correct me if am wrong, Drew !
August 10, 2010 at 11:08 pm
LutzM (8/10/2010)
Drew,I guess the Row_Number approach I was hitting for was overdesigned for the task...
You're absolutely right, for the given task a simple join on the time columns would be enough.
No Lutz, ROW_NUMBER is essential , IMHO , for this request. Please take a look at the above post i made ; If the data in OP's table is wat i posted in the sample data, then ROW_NUMBER (or anyother row number allocation mechanism) + a CROSS JOIN / CROSS APPLY will be required. INNER JOINS wont help, IMO...Correct me if i am wrong!
LutzM (8/10/2010)
That's why I usually insist in getting ready to use sample data...One thing the OP should be aware of though: after the first update it's possible to end up with rows where starttime = endtime. But that's a different story.
You are absolutely right! When the OP does not give any sample data to work with, then 50% of the times he/she will get untested and un-optimized answer only. Sample data along with the constraints + a clear desired results are a MUST!
LutzM (8/10/2010)
@ColdCoffee:Sorry for sending you in the wrong direction!
Not at all, Lutz. I already coded the sample data with IDENTITY column , so that i will get a ROW ID allocated to a row. ROW_NUMBER is a mandate, i beleive, for this problem... And Lutz, u wont me send me in wrong direction ever, will you ??? You are Goliath in SQL, mate! 🙂
August 10, 2010 at 11:50 pm
ColdCoffee (8/10/2010)
INNER JOIN will fail with the following sample data :
A CROSS JOIN with a WHERE clause that references both tables is essentially the same as an INNER JOIN.
Please correct me if am wrong, Drew !
I think we need to apply Occam's Razor here. All things being equal, the simplest solution is usually correct. The OP has given no indication that the simple solution doesn't work.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 11, 2010 at 3:36 am
drew.allen (8/10/2010)
ColdCoffee (8/10/2010)
INNER JOIN will fail with the following sample data :A CROSS JOIN with a WHERE clause that references both tables is essentially the same as an INNER JOIN
Drew
True, i dint apply my mind.. lol...
Probably for larger dataset CROSS JOIN will crib.. but u know, i love CROSS JOIN :w00t:
August 11, 2010 at 5:00 am
ColdCoffee (8/10/2010)
LutzM (8/10/2010)
@ColdCoffee:Sorry for sending you in the wrong direction!
Not at all, Lutz. I already coded the sample data with IDENTITY column , so that i will get a ROW ID allocated to a row. ROW_NUMBER is a mandate, i beleive, for this problem... And Lutz, u wont me send me in wrong direction ever, will you ??? You are Goliath in SQL, mate! 🙂
Now let's see how long it'll take before I meet David and his catapult... :unsure: 😉
August 11, 2010 at 5:07 am
LutzM (8/11/2010)
ColdCoffee (8/10/2010)
LutzM (8/10/2010)
@ColdCoffee:Sorry for sending you in the wrong direction!
Not at all, Lutz. I already coded the sample data with IDENTITY column , so that i will get a ROW ID allocated to a row. ROW_NUMBER is a mandate, i beleive, for this problem... And Lutz, u wont me send me in wrong direction ever, will you ??? You are Goliath in SQL, mate! 🙂
Now let's see how long it'll take before I meet David and his catapult... :unsure: 😉
I am pretty sure when that happens, Goliath will emerge victorious! Records are meant to be broken, aren't they ?? 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply