sql - Calculate Average time spend based on a change in location zone -

sql - Calculate Average time spend based on a change in location zone -

i have table similar

create table lochist ( res_id varchar(10) not null, loc_date timestamp not null, loc_zone varchar(10) )

with values such

insert lochist values(0911,2015-09-23 12:27:00.000000,sylvsylga); insert lochist values(5468,2013-02-15 13:13:24.000000,30726); insert lochist values(23894,2013-02-15 13:12:13.000000,bectfounc); insert lochist values(24119,2013-02-15 13:12:09.000000,30363); insert lochist values(7101,2013-02-15 13:11:37.000000,37711); insert lochist values(26083,2013-02-15 13:11:36.000000,shawandal); insert lochist values(24978,2013-02-15 13:11:36.000000,38132); insert lochist values(26696,2013-02-15 13:11:27.000000,29583); insert lochist values(5468,2013-02-15 13:11:00.000000,37760); insert lochist values(5552,2013-02-15 13:10:55.000000,30090); insert lochist values(24932,2013-02-15 13:10:48.000000,jbttlitga); insert lochist values(23894,2013-02-15 13:10:42.000000,47263); insert lochist values(26803,2013-02-15 13:10:25.000000,32534); insert lochist values(24434,2013-02-15 13:10:03.000000,plansufva); insert lochist values(26696,2013-02-15 13:10:00.000000,georalbga); insert lochist values(5468,2013-02-15 13:09:54.000000,19507); insert lochist values(23894,2013-02-15 13:09:48.000000,37725);

this table literally goes on millions of records.

each res_id represents id of trailer pings location loc_zone stored @ time in loc_date.

what trying find, average amount of time spent trailers in specific location zone. example, if trailer x spent 4 hours in in loc zone plansufva, , trailer y spent 6 hours in loc zone plansufva want return

loc zone avg time plansufva 5

is there anyway without cursors?

i appreciate help.

to solve problem, need amount of time spent @ each location.

one way correlated subquery. need grouping adjacent values. thought find next value in sequence:

select resid, min(loc_zone) loc_zone, min(loc_date) starttime, max(loc_date) endtime, nextdate nextstarttime (select lh.*, (select min(loc_date) lochist lh2 lh2.res_id = lh.res_id , lh2.loc_zone <> lh.loc_zone , lh2.loc_date > lh.loc_date ) nextdate lochist lh ) lh grouping lh.res_id, nextdate

with data, can average want.

i not clear if time should based on endtime - starttime (last recorded time @ location minus first recorded time) or nextstarttime - starttime (first time @ next location minus first time @ location).

also, returns null lastly location each res_id. don't lastly in sequence.

if build index on res_id, loc_date, loc_zone, might run faster.

if had oracle or sql server 2012, right query is:

select lh.*, lead(loc_date) on (partition res_id order loc_date) nextdate (select lh.*, lag(loc_zone) on (partition res_id order loc_date) prevzone lochist lh ) lh prevzone null or prevzone <> loc_zone

now have 1 row per remain , nextdate date @ next zone.

sql sql-server