Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Elapsed time Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, September 4, 2013 11:07 AM
 SSC-Dedicated Group: Administrators Last Login: Yesterday @ 1:59 PM Points: 34,363, Visits: 18,582
 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 help
Post #1491445
 Posted Wednesday, September 4, 2013 6:30 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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
Post #1491594
 Posted Wednesday, September 4, 2013 6:38 PM
 SSC-Dedicated Group: Administrators Last Login: Yesterday @ 1:59 PM Points: 34,363, Visits: 18,582
 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 help
Post #1491596
 Posted Wednesday, September 4, 2013 7:07 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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
Post #1491605
 Posted Wednesday, September 4, 2013 7:21 PM
 SSC-Dedicated Group: Administrators Last Login: Yesterday @ 1:59 PM Points: 34,363, Visits: 18,582
Post #1491608
 Posted Friday, September 6, 2013 8:32 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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
Post #1492467
 Posted Tuesday, September 10, 2013 6:38 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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
Post #1493408
 Posted Monday, September 16, 2013 7:44 AM
 SSC-Dedicated Group: Administrators Last Login: Yesterday @ 1:59 PM Points: 34,363, Visits: 18,582