How to query elapsed time sequential history moments.

[Executor]

Query elapsed time sequential history moments.

Hi to All,

I have a Table "StatusHistory"designed as:

PK, AutoNumer, Primary Key
FK_Invoice, Long, Foreign Key to Invoices_Table
FK_Status, Long, Foreign Key to Statuses_Table
Date_Assigned, DateTime, Date the status changed.

I have these records:

1, 1, 1, 23 march 2005
2, 1, 2, 25 march 2005
3, 1, 3, 28 march 2005
4, 2, 1, 24 march 2005
5, 1, 4, 30 march 2005
6, 2, 2, 25 march 2005

I want to query the time elapsed between each status change.

Invoice, StatusFrom, DateFrom, StatusTo, DateTo, Elapsed.

I have tried:

SELECT I.FK_Invoice, I.FK_Status AS StatusFrom, I.Date_Assigned AS
DateFrom, S.FK_Status AS StatusTo, Min(S.Date_Assigned) AS DateTo,
DateDiff("d",[DateFrom],[dateTo]) AS Elapsed
FROM StatusHistory AS I INNER JOIN StatusHistory AS S ON I.FK_Invoice =
S.FK_Invoice
GROUP BY I.FK_Invoice, I.FK_Status, I.Date_Assigned, S.FK_Status
HAVING (((Min(S.Date_Assigned))>[I.Date_Assigned]));

Result:
FK_Invoice; StatusFrom; DateFrom; StatusTo; DateTo; Elapsed
1; 1; 23-3-2005; 2; 25-3-2005; 2
1; 1; 23-3-2005; 3; 28-3-2005; 5
1; 1; 23-3-2005; 4; 30-3-2005; 7
1; 2; 25-3-2005; 3; 28-3-2005; 3
1; 2; 25-3-2005; 4; 30-3-2005; 5
1; 3; 28-3-2005; 4; 30-3-2005; 2
2; 1; 24-3-2005; 2; 25-3-2005; 1


But this will give me also the time elapsed between the first and de
third record and the time between the first and the fifth record. I am
not interested in these two.
I can not use the difference between two status values, because I do
not know what the status values will be when the program is in use. And
I suspect that at some time the status of an invoice can be set back in
reaction on a complaint by the customer.

So my question is: How to get only the sequential dates from my status
history?

Thank in advance,

 

[Allen Browne]

If you do not mind a read-only result, you could use a subquery to get the
previous date for the same invoice, like this:

SELECT FK_Invoice,
FK_Status AS StatusTo,
Date_Assigned AS DateTo,
(SELECT TOP 1 Date_Assigned FROM StatusHistory AS Dupe
WHERE Dupe.FK_Invoice = StatusHistory.FK_Invoice
AND Dupe.Date_Assigned < StatusHistory.Date_Assigned
ORDER BY Dupe.Date_Assigned DESC, Dupe.PK) AS DateFrom,
(SELECT TOP 1 FK_Status FROM StatusHistory AS Dupe
WHERE Dupe.FK_Invoice = StatusHistory.FK_Invoice
AND Dupe.Date_Assigned < StatusHistory.Date_Assigned
ORDER BY Dupe.Date_Assigned DESC, Dupe.PK) AS StatusFrom,
DateDiff("d", [DateFrom], [Date_Assigned]) AS Elapsed
FROM StatusHistory

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Other alternatives in:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm


2 other alternatives:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Query elapsed time sequential history moments.

Hi to All,

I have a Table "StatusHistory"designed as:

PK, AutoNumer, Primary Key
FK_Invoice, Long, Foreign Key to Invoices_Table
FK_Status, Long, Foreign Key to Statuses_Table
Date_Assigned, DateTime, Date the status changed.

I have these records:

1, 1, 1, 23 march 2005
2, 1, 2, 25 march 2005
3, 1, 3, 28 march 2005
4, 2, 1, 24 march 2005
5, 1, 4, 30 march 2005
6, 2, 2, 25 march 2005

I want to query the time elapsed between each status change.

Invoice, StatusFrom, DateFrom, StatusTo, DateTo, Elapsed.

I have tried:

SELECT I.FK_Invoice, I.FK_Status AS StatusFrom, I.Date_Assigned AS
DateFrom, S.FK_Status AS StatusTo, Min(S.Date_Assigned) AS DateTo,
DateDiff("d",[DateFrom],[dateTo]) AS Elapsed
FROM StatusHistory AS I INNER JOIN StatusHistory AS S ON I.FK_Invoice =
S.FK_Invoice
GROUP BY I.FK_Invoice, I.FK_Status, I.Date_Assigned, S.FK_Status
HAVING (((Min(S.Date_Assigned))>[I.Date_Assigned]));

Result:
FK_Invoice; StatusFrom; DateFrom; StatusTo; DateTo; Elapsed
1; 1; 23-3-2005; 2; 25-3-2005; 2
1; 1; 23-3-2005; 3; 28-3-2005; 5
1; 1; 23-3-2005; 4; 30-3-2005; 7
1; 2; 25-3-2005; 3; 28-3-2005; 3
1; 2; 25-3-2005; 4; 30-3-2005; 5
1; 3; 28-3-2005; 4; 30-3-2005; 2
2; 1; 24-3-2005; 2; 25-3-2005; 1


But this will give me also the time elapsed between the first and de
third record and the time between the first and the fifth record. I am
not interested in these two.
I can not use the difference between two status values, because I do
not know what the status values will be when the program is in use. And
I suspect that at some time the status of an invoice can be set back in
reaction on a complaint by the customer.

So my question is: How to get only the sequential dates from my status
history?

Thank in advance,

 

[Executor]

Thanks Mate

Works fine.

I added some small things for sorting and skipping blank for Status
from:

SELECT StatusHistory.FK_Invoice, StatusHistory.FK_Status AS StatusTo,
StatusHistory.Date_Assigned AS DateTo, (SELECT TOP 1 Date_Assigned FROM
StatusHistory AS Dupe
WHERE Dupe.FK_Invoice = StatusHistory.FK_Invoice
AND Dupe.Date_Assigned < StatusHistory.Date_Assigned
ORDER BY Dupe.Date_Assigned DESC, Dupe.PK) AS DateFrom, (SELECT TOP
1 FK_Status FROM StatusHistory AS Dupe
WHERE Dupe.FK_Invoice = StatusHistory.FK_Invoice
AND Dupe.Date_Assigned < StatusHistory.Date_Assigned
ORDER BY Dupe.Date_Assigned DESC, Dupe.PK) AS StatusFrom,
DateDiff("d",[DateFrom],[Date_Assigned]) AS Elapsed
FROM StatusHistory
WHERE ((((SELECT TOP 1 Date_Assigned FROM StatusHistory AS Dupe
WHERE Dupe.FK_Invoice = StatusHistory.FK_Invoice
AND Dupe.Date_Assigned < StatusHistory.Date_Assigned
ORDER BY Dupe.Date_Assigned DESC, Dupe.PK)) Is Not Null))
ORDER BY StatusHistory.FK_Invoice;