| Author |
 Topic  |
|
|
azoo123
Starting Member
2 Posts |
 Posted - 02/15/2013 : 13:41:48
|
I have a table similiar to
create table LOCHIST
(
RES_ID VARCHAR(10) NOT NULL,
LOC_DATE TIMESTAMP NOT NULL,
LOC_ZONE VARCHAR(10)
)with values such as
insert into LOCHIST values(0911,2015-09-2312:27:00.000000,SYLVSYLGA);
insert into LOCHIST values(5468,2013-02-15 13:13:24.000000,30726);
insert into LOCHIST values(23894,2013-02-15 13:12:13.000000,BECTFOUNC);
insert into LOCHIST values(24119,2013-02-15 13:12:09.000000,30363);
insert into LOCHIST values(7101,2013-02-15 13:11:37.000000,37711);
insert into LOCHIST values(26083,2013-02-15 13:11:36.000000,SHAWANDAL);
insert into LOCHIST values(24978,2013-02-15 13:11:36.000000,38132);
insert into LOCHIST values(26696,2013-02-15 13:11:27.000000,29583);
insert into LOCHIST values(5468,2013-02-15 13:11:00.000000,37760);
insert into LOCHIST values(5552,2013-02-15 13:10:55.000000,30090);
insert into LOCHIST values(24932,2013-02-15 13:10:48.000000,JBTTLITGA);
insert into LOCHIST values(23894,2013-02-15 13:10:42.000000,47263);
insert into LOCHIST values(26803,2013-02-15 13:10:25.000000,32534);
insert into LOCHIST values(24434,2013-02-15 13:10:03.000000,PLANSUFVA);
insert into LOCHIST values(26696,2013-02-15 13:10:00.000000,GEORALBGA);
insert into LOCHIST values(5468,2013-02-15 13:09:54.000000,19507);
insert into LOCHIST values(23894,2013-02-15 13:09:48.000000,37725);
This table literally goes on for millions of records.
Each RES_ID represents the ID of a trailer who pings their location to a LOC_ZONE which is then stored at the time in LOC_DATE.
What I am trying to find, is the average amount of time spent for all trailers in a specific location zone. For example, if trailer x spent 4 hours in in loc zone PLANSUFVA, and trailer y spent 6 hours in loc zone PLANSUFVA I would want to return
Loc Zone Avg Time
PLANSUFVA 5
Is there anyway to do this without cursors?
I really appreciate your help.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/15/2013 : 14:25:45
|
yep
you can do like
SELECT l1.LOC_ZONE,AVG(DATEDIFF(hh,l1.LOC_DATE,COALESCE(l2.MinDate,GETDATE()))*1.0) AS timeDiff
FROM LOCHIST l1
OUTER APPLY (SELECT MIN(LOC_DATE) AS MinDate
FROM LOCHIST
WHERE RES_ID = l1.RES_ID
AND LOC_DATE > l1.LOC_DATE
AND LOC_ZONE <> l1.LOC_ZONE
)l2
GROUP BY l1.LOC_ZONE
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
| |
Topic |
|
|
|
SQL Calculate Average time spend based on a change
