SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Calculate Average time spend based on a change
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

azoo123
Starting Member

2 Posts

Posted - 02/15/2013 :  13:41:48  Show Profile  Reply with Quote
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
52323 Posts

Posted - 02/15/2013 :  14:25:45  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000