Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
azoo123
Starting Member
2 Posts |
Posted - 2013-02-04 : 17:17:28
|
Hey all,I am trying to go through this table, and then for every change in LOC_Zone, record the average amount of time that a certain loczone takes up. For example, for the UTILGLAVA loc_zone I would need to sum the amount of time that has passed until the next loc zone appears (in this case 29102). I would then sum the amount of time spent in 29102 until the next loc zone (integeosc). The process would repeat with the average time spent in each loc_zone being my final desired result. Here is an image of the table for clarification:[url]http://imgur.com/m8h7gQJ][/url]Is this possible to do in sql alone, or do I need to use some other scripting language?Thanks for your help!-Andrew |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-04 : 17:34:46
|
How do you calculate the time spent in a given zone? In your screen shot I see a LOC_DATE; is that what needs to be used? For example, if you take LOC_ZONE = 29102, which has just one time stamp, 2011-06-15 04:06:57, what is the average time for that LOC_ZONE? Perhaps the difference between the next timestamp (for INTEGEOSC) and the one timestamp for LOC_ZONE?It would add a lot of clarity if you posted what the expected result for the sample data is. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
|
|