## Elapsed time

 Author Message Steve Jones SSC Guru Group: Administrators Points: 331073 Visits: 20112 Show a series of start and stop times, listed for a group of people. calculate the elapsed times (or dates) and order them.`create table Runners( runnerid int, timemark time) insert runners (runnerid, timemark) values (1, '12:00'), (2, '12:00'), (3, '12:00'), (4, '12:00'), (1, '12:15'), (2, '12:17'), (3, '12:16'), (4, '12:15')` Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com dwain.c SSC-Forever Group: General Forum Members Points: 44397 Visits: 6431 Will each runner have at most 2 entries? If only one entry (start time), then what would you like to see for elapsed time?If more than 2, does the latest time count as the end time?And what if it's a marathon that starts at 23:00 and runs over to the next day? My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Steve Jones SSC Guru Group: Administrators Points: 331073 Visits: 20112 Feel free to handle this as a couple pieces, perhaps in different ways.For the first, let's say two times for each runner, could cross days.If you want to tackle a second, make it more open ended. allow for errors, or assume there are splits and the last time is the end time. Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com dwain.c SSC-Forever Group: General Forum Members Points: 44397 Visits: 6431 I'll give it a look this weekend. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Steve Jones SSC Guru Group: Administrators Points: 331073 Visits: 20112 Thanks, and let me know. Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com dwain.c SSC-Forever Group: General Forum Members Points: 44397 Visits: 6431 I've made a submission.I hope it's what you were looking for and more importantly I hope I didn't miss some kind of trick part of the question. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSC-Forever Group: General Forum Members Points: 44397 Visits: 6431 Interesting that this related thread should appear just now:http://www.sqlservercentral.com/Forums/Topic1492737-3077-1.aspx?Update=1 My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Steve Jones SSC Guru Group: Administrators Points: 331073 Visits: 20112 Thanks, I'll look it over this week. Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com dwain.c SSC-Forever Group: General Forum Members Points: 44397 Visits: 6431 I see you accepted it now for publication. That's cool!I noticed that I misspelled "Spackle" as "Spacklet" in the title. I hope you can correct that before publication.I don't want to mess with it myself now that it's been accepted (don't know what would happen to the status). My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables