Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Change Calculation

Status
Not open for further replies.

Rseven

Technical User
Mar 7, 2006
41
US
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
 
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
 
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
 
What is the SQL code of your queries ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
A starting point for your query:

SQL:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top