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-15 : 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 returnLoc Zone Avg Time PLANSUFVA 5Is there anyway to do this without cursors? I really appreciate your help. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 14:25:45
|
yepyou can do likeSELECT l1.LOC_ZONE,AVG(DATEDIFF(hh,l1.LOC_DATE,COALESCE(l2.MinDate,GETDATE()))*1.0) AS timeDiffFROM LOCHIST l1OUTER 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 )l2GROUP BY l1.LOC_ZONE ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|