Date Change Calculation

[Rseven]

Good afternoon All,

I am trying to calculate the incremental increase (in months) at each change of the CURRENT_TARGET_DT for a given ISSUE_ID with multiple occurances on different rows. Can anyone give me an idea? Basically, looking at the data below I would need to calculate the difference between 7/21/2012 and 7/21/2013, then calcuate it for 7/21/2013 and 3/14/2014. Any help would be appreciated.

[pre]ISSUE_ID CURRENT_TARGET_DT
IS-622316 7/21/2012
IS-622316 7/21/2013
IS-622316 3/14/2014[/pre]

thanks in advance for your time

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Tip: have a look at the DateDiff function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rseven]

Unfortunately I have not started the code as I am not 100% sure where to begin. I have written the queries that group the IDs and the dates together, but because they are seperate entities, I am unsure how to apply the DateDiff function to the records.

I apologize if there wasn't enough information provided, but was just looking for an idea not a solution.

Thanks

 

[PHV]

What is the SQL code of your queries ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

A starting point for your query:

SELECT ttRSeven.ISSUE_ID, ttRSeven.CURRENT_TARGET_DT, 
(SELECT Max(CURRENT_TARGET_DT)
 FROM ttrSeven B
 WHERE B.ISSUE_ID = ttRSeven.ISSUE_ID and B.CURRENT_TARGET_DT < ttRSeven.CURRENT_TARGET_DT) AS PreviousDate
FROM ttRSeven;

You can use this query as the source and use DateDiff() to calculate the month difference between the current and previous columns.

Duane
Hook'D on Access
MS Access MVP

 

[Rseven]

Thanks dhookom, my biggest obstacle was to get the dates on the same row for the calculation. Worked great!!

PH, thanks for the input also. I will be sure to provide more information going forward

Thanks again to you both
Rico