tdanley (9/6/2013)
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.
No problem. Everybody was new around here at one point in time. It is hard to know what to post for these types of questions.
That does help clarify. I had a feeling you wanted that but I have been bitten so many times by coding a solution to the wrong problem. 😛
Unless I am mistaken, the excellent code that Alan posted should do exactly that. Well except that no matter how I look at it it seems that stop #3 should be 3 hours. 7:26am - 10:38am
If his code does not produce the desired output post back and we will figure out what we can do to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/