Calculating Number of Days Between Records

[joehands1980]

Hi, looking for a bit of help, I am trying to calculate the number of days between incidents and was wondering if anyone could help out on how to run this.

If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.

Hope this makes sense.

Cheers
Joe

 

[namliam]

if you have an incrementing incident number you can so a self join query and simply substract the two dates from eachother

 

[pr2-eugin]

joehands1980, Welcome to AWF..

How is your Data organized or stored in the tables? You need DateDiff function to get the Difference in Dates.

 

[joehands1980]

I have a record with a unique ID of when someone had an accident, a new record is created for the next one, as so on each with different dates and I want to say the first to second was 10 days, second to third was 15 days, I have worked out the date diff between them but struggling on how to get the sorted to calculate between second record to third record etc

 

[pr2-eugin]

Something along these lines should do the trick..

SELECT yourTableName.yourUniqueID, Min(yourTableName.dateFieldName) AS MinOfdateFieldName, Max(yourTableName.dateFieldName) AS MaxOfdateFieldName, DateDiff("d", MinOfdateFieldName, MaxOfdateFieldName) As TotalDays
FROM yourTableName
GROUP BY yourTableName.yourUniqueID;

Replace the yourTableName and all fields names to match your table design.

 

[joehands1980]

Something along these lines should do the trick..

SELECT yourTableName.yourUniqueID, Min(yourTableName.dateFieldName) AS MinOfdateFieldName, Max(yourTableName.dateFieldName) AS MaxOfdateFieldName, DateDiff("d", MinOfdateFieldName, MaxOfdateFieldName) As TotalDays
FROM yourTableName
GROUP BY yourTableName.yourUniqueID;

Replace the yourTableName and all fields names to match your table design.

Cheers will give it a go just now

 

[namliam]

select ...
from yourtable NewInc
left join yourtable PrevInc on NewInc.UniqueID = PrevInc.UniqueID + 1

this will put your previous incident on the same record/line as your new one and you can easily "datediff" the two dates.

This DOES require the UniqueID to be sequential.... 1,2,3,4,5,etc.
If you have 1,6,9,15 it wont work as expected

 

[joehands1980]

select ...
from yourtable NewInc
left join yourtable PrevInc on NewInc.UniqueID = PrevInc.UniqueID + 1

this will put your previous incident on the same record/line as your new one and you can easily "datediff" the two dates.

This DOES require the UniqueID to be sequential.... 1,2,3,4,5,etc.
If you have 1,6,9,15 it wont work as expected

These take place in different locations, so as as I bring this factor in it might not work but it should give me what I need just now with some manual intervention, I will try this also, thanks much

 

[joehands1980]

Something along these lines should do the trick..

SELECT yourTableName.yourUniqueID, Min(yourTableName.dateFieldName) AS MinOfdateFieldName, Max(yourTableName.dateFieldName) AS MaxOfdateFieldName, DateDiff("d", MinOfdateFieldName, MaxOfdateFieldName) As TotalDays
FROM yourTableName
GROUP BY yourTableName.yourUniqueID;

Replace the yourTableName and all fields names to match your table design.

It just gives me back zero's as it is calculating the min and max on the same incident date

 

[pr2-eugin]

So is your data not stored like this?

logID    dupDialedDate    dupRAW_ID    
715        05/12/2012    4255    
714        05/12/2012    4291    
9353       10/01/2013    5247    
762        05/12/2012    6213    
772        [COLOR=Red][B]05/12/2012[/B][/COLOR]    [COLOR=Red][B]6307[/B][/COLOR]    
775        05/12/2012    6307    
776        05/12/2012    6307    
779        [COLOR=Red][B]09/12/2012[/B][/COLOR]    [COLOR=Red][B]6307[/B][/COLOR]    
711        05/12/2012    6699    
27         05/12/2012    7769    
28         05/12/2012    7769    
764        05/12/2012    7785    
710        05/12/2012    7907

If that's the case, your result should be..

dupRAW_ID    MinOfdupDialedDate    MaxOfdupDialedDate    TotalDays
4255        05/12/2012                05/12/2012        0
4291        05/12/2012                05/12/2012        0
5247        10/01/2013                10/01/2013        0
6213        05/12/2012                05/12/2012        0
[COLOR=Red][B]6307        05/12/2012[/B][/COLOR]        [COLOR=Red][B]        09/12/2012    [/B][/COLOR]    [COLOR=Red][B]4[/B][/COLOR]
6699        05/12/2012                05/12/2012        0
7769        05/12/2012                05/12/2012        0
7785        05/12/2012                05/12/2012        0
7907        05/12/2012                05/12/2012        0

 

[joehands1980]

no at present it is stored

UniqueID Date Location DaysBetween
1 01/01/2013 1 0
2 02/01/2013 2 0
3 13/01/2013 3 0
4 19/01/2013 1 18
5 25/01/2013 2 23
6 31/01/2013 3 18

The days between is my desired outcome, so I need it to look at record 1 location and where it appears again, calculate between the two dates

 

[pr2-eugin]

So your Query should be..

SELECT yourTableName.Location, Min(yourTableName.[Date]) AS MinOfDate, Max(yourTableName.[Date]) AS MaxOfDate, DateDiff("d", MinOfDate, MaxOfDate) As DaysBetween
FROM yourTableName
GROUP BY yourTableName.Location;

Change yourTableName to the name of your table.. Date is a very bad field name.. Stick a Good Naming standard..

 

[joehands1980]

So your Query should be..

SELECT yourTableName.Location, Min(yourTableName.[Date]) AS MinOfDate, Max(yourTableName.[Date]) AS MaxOfDate, DateDiff("d", MinOfDate, MaxOfDate) As DaysBetween
FROM yourTableName
GROUP BY yourTableName.Location;
 
The date was just for reference, its not named that, that works a treat and gives me the days between the first and last records but it misses out the ones in between, for one location I have 16 records spanning different months all with different ID fields and dates, I need to count the days between each of the records so first date to second date, second date to third date based on the ID field being a higher number than the previous one, sorry to be a pain but would you have given me works great and on the right track :D

 

[namliam]

select ...
from yourtable NewInc
left join yourtable PrevInc on NewInc.UniqueID = PrevInc.UniqueID + 1

this will put your previous incident on the same record/line as your new one and you can easily "datediff" the two dates.

This DOES require the UniqueID to be sequential.... 1,2,3,4,5,etc.
If you have 1,6,9,15 it wont work as expected

well to make this more flexible... I created your table and just called it Table1... so rename to whatever you have.

SELECT Table1.UniqueID AS CurrUniqueID, Table1_1.UniqueID AS PrevUniqueID
FROM Table1 AS Table1_1 
INNER JOIN Table1 ON Table1_1.location = Table1.location
WHERE (((Table1_1.UniqueID)<[table1].[UniqueID]))
ORDER BY Table1.UniqueID, Table1_1.UniqueID;

save this query as qryLocation

SELECT qryLocation.CurrUniqueID, Max(qryLocation.PrevUniqueID) AS MaxOfPrevUniqueID
FROM qryLocation
GROUP BY qryLocation.CurrUniqueID;

save this query as qryPrevInc


Your final query:

SELECT Table1.UniqueID, Table1.IncDate, Table1.location, Table1_1.UniqueID, Table1_1.IncDate, Table1_1.location, [table1].[incdate]-[table1_1].[incdate] AS Expr1
FROM (Table1 
LEFT JOIN qryPrevInc ON Table1.UniqueID = qryPrevInc.CurrUniqueID) 
LEFT JOIN Table1 AS Table1_1 ON qryPrevInc.MaxOfPrevUniqueID = Table1_1.UniqueID;

** Disclaimer **
This may not scale perfectly to millions of records, but it should work decently for a limited scope...

 

[namliam]

If you want to make this "more scalable" instead of dynamicaly finding the previous incident on the fly like this you may want to "find" the previous incident on the location at the moment in time the (new) incident is created actually storing the ID in the table as an additional column so you can find it easily.

 

[joehands1980]

well to make this more flexible... I created your table and just called it Table1... so rename to whatever you have.

SELECT Table1.UniqueID AS CurrUniqueID, Table1_1.UniqueID AS PrevUniqueID
FROM Table1 AS Table1_1 
INNER JOIN Table1 ON Table1_1.location = Table1.location
WHERE (((Table1_1.UniqueID)<[table1].[UniqueID]))
ORDER BY Table1.UniqueID, Table1_1.UniqueID;

save this query as qryLocation

SELECT qryLocation.CurrUniqueID, Max(qryLocation.PrevUniqueID) AS MaxOfPrevUniqueID
FROM qryLocation
GROUP BY qryLocation.CurrUniqueID;

save this query as qryPrevInc


Your final query:

SELECT Table1.UniqueID, Table1.IncDate, Table1.location, Table1_1.UniqueID, Table1_1.IncDate, Table1_1.location, [table1].[incdate]-[table1_1].[incdate] AS Expr1
FROM (Table1 
LEFT JOIN qryPrevInc ON Table1.UniqueID = qryPrevInc.CurrUniqueID) 
LEFT JOIN Table1 AS Table1_1 ON qryPrevInc.MaxOfPrevUniqueID = Table1_1.UniqueID;

** Disclaimer **
This may not scale perfectly to millions of records, but it should work decently for a limited scope...

Your a star this works perfectly for my issue, thanks very much for your help