October 23, 2007 at 3:13 pm
Hi,
I was wondering if someone could help point me in the correct direction for a query I need to create.
I need to be able to determine the average number of days between different dates (logins) for people listed in one of our tables. I'm not sure what is the best way to write something like this. What I am looking for is listed below in this sample emp_login table.
emp_login
[font="Courier New"]
name login_dt emp_id
mary 10/01/2006 1
john 10/13/2006 2
john 12/17/2006 2
bob 01/01/2006 3
tom 04/12/2007 4
mary 11/01/2006 1
mary 11/15/2006 1
tom 09/01/2007 4
bob 10/30/2006 3
bob 12/15/2006 3
john 03/01/2007 2
mary 09/30/2007 1
[/font]
I need to be able to query all employees in the table. For example: mary, I need to take the difference between her login dates by comparing the oldest login date and compare it to the second oldest (which returns 31 days), then compare that second oldest to the third oldest (which is 14 days) and finally third oldest to most recent (which is 319 days). These are then added together (364 days) and averaged by dividing by 3 (121 days average). Then I need to return a value for every employee in the table and everything I have tried with a loop has not produced any results at all.
I'm not sure how to structure the query to compare one login date to the next one selected and so forth. I hope this makes sense.
Any help is greatly appreciated.
October 23, 2007 at 10:03 pm
DECLARE @a TABLE (Name char(4), Date datetime, ID int)
INSERT INTO @a SELECT 'mary', '10/01/2006', 1
UNION SELECT 'john', '10/13/2006', 2
UNION SELECT 'john', '12/17/2006', 2
UNION SELECT 'bob', '01/01/2006', 3
UNION SELECT 'tom', '04/12/2007', 4
UNION SELECT 'mary', '11/01/2006', 1
UNION SELECT 'mary', '11/15/2006', 1
UNION SELECT 'tom', '09/01/2007', 4
UNION SELECT 'bob', '10/30/2006', 3
UNION SELECT 'bob', '12/15/2006', 3
UNION SELECT 'john', '03/01/2007', 2
UNION SELECT 'mary', '09/30/2007', 1
SELECT C.ID, C.Name, AVG(C.Days)
FROM
(
SELECT B.ID, B.Name, B.Date1, B.Date2, Days = DATEDIFF(day, B.Date2, B.Date1)
FROM
(
SELECT A.ID, A.Name, Date1 = A.Date, Date2 = (SELECT MAX(Z.Date) FROM @a Z WHERE Z.ID = A.ID AND Z.Date < A.Date)
FROM @a A
) B
WHERE B.Date2 IS NOT NULL
) C
GROUP BY C.ID, C.Name
ORDER BY C.ID, C.Name
October 24, 2007 at 6:36 am
If you actually want to have a query which returns the number of days between days, then I suggest you use something like:
with l as
(select *, row_number() over (partition by emp_id order by login_dt) as rn
from emp_login)
select l1.*, datediff(day, l1.login_dt, l2.login_dt) as days
from l l1 join l l2 on l1.emp_id = l2.emp_id and l1.rn = l2.rn - 1
;
This uses a row_number() function to order the logins for each user, and then joins the result back to itself.
But the trick here is really to consider the number of days between the first and last, and divide it by the number of logins less one (ie, the number of gaps). If someone logged in on Mon,Tue,Fri, or Mon,Thu,Fri the average gap is still two days.
So something like:
select emp_id, datediff(day,min(login_dt),max(login_dt)) / (count(*) - 1)
from emp_login l
group by emp_id
having count(*) >= 2
;
--where you're only considering employees with multiple logins of course...
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply