my apologies for the lack of code in my original post. Newbie mistake I haven't made many posts and duly noted. However what you have is exactly right.
The first line has a PUPDRP type of "PUP". The stop time is the time the truck stopped the loading facility (PUP = Pick Up). The 3 remaining lines are designated DRP meaning "drop" so the times in the stop time there is the time that the truck stopped the drop location. I need to calculate how long it was between the time the truck loaded, and each stop.
Time between loading and stop 1 = 1 hrs
time between loading and stop 2 = 2 hrs
time between loading an stop 3 = 2 hrs
I know that every order has one pickup and potentially multiple drop offs.
Hope that helps clarify things.
Sean Lange (9/6/2013)
Let's start with some ddl and actual sample data. The problem with just posting some characters is we have no idea what the datatypes are or where the column breaks are. If you instead post code to create a table and then fill that table with inserts there is no room for error. The other advantage is that you will have a lot more people willing to help.I have no idea if this is right because I had to make some guesses.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
OrderNum int,
Location varchar(10),
PUPDRP char(3),
SomeValueOfSomething int,
StopTime datetime,
ColumnIMadeUP tinyint
)
insert #Something
select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all
select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all
select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all
select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2
select * from #Something
If that is not right then please modify this and post it the format that it should be.
If this is correct can you please explain what you mean "What I need to do is calculate the time between the PUP time, and each DRP time."
I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.
You absolutely 100% do NOT need a cursor for this. Cursors have their place but straight data manipulation is not it. They are just too slow.