Max day apart between 2 consecutive records by employee

[zx3]

Hi all,



I have a project i need to get records that have the maximum days interval by employee

Say i start with the following

dt	emp	amt
1-Dec-14	aa	11
1-Dec-14	bb	2
28-Nov-14	aa	32
27-Nov-14	bb	14
26-Nov-14	bb	32
10-Nov-14	aa	18

dt is date, emp is employee name, amt is sales amount dapart is days apart

the query has to get the following result

dt	emp	amt	dapart
28-Nov-14	aa	32	18
1-Dec-14	bb	2	4

28 nov14 is for aa because it's 18 days apart from prev aa's record and 3 days apart from next aa's record.

Please help.

[June7]

One way is with nested subquery, review http://allenbrowne.com/subquery-01.html#AnotherRecord

Another way might be with domain aggregate DMax(), however domain aggregates can be slower performers.

[zx3]

Thank June7 for the link, based on the link, i made a workable query as following

SELECT f.dt, f.emp, f.amt, f.apart as dapart FROM(
SELECT dt, dt-pd AS [apart], emp, amt FROM (
SELECT dt, emp, amt, 
(SELECT MAX(dt) FROM TA T1 WHERE T1.emp = T.emp AND T1.dt < T.dt) AS pd 
FROM TA AS T)) AS f INNER JOIN (
SELECT emp, Max(apart) as dapart FROM(
SELECT dt, dt-pd AS [apart], emp, amt FROM (
SELECT dt, emp, amt, 
(SELECT MAX(dt) FROM TA T1 WHERE T1.emp = T.emp AND T1.dt < T.dt) AS pd 
FROM TA AS T))
GROUP BY emp
) AS l ON (f.emp=l.emp) and (f.apart=l.dapart);

It sounds too complicated although it works, hope some expert here can refine it bit to make it simple, or else after a day i will close this thread as solved.
Thank you guys