
Re: How to calculate the number of months between two dates
Friedrich Hofmann Mar 11, 2015 8:22 AM (in response to Rekha Harish )Hi rekha,
do you want to consider the exact day or only the month that the resp. date is in?
The former would be somewhat complicated as a month does not always have the same nr. of days  how many months would 3 days be (if the to_date is the 3rd of any month)?
The latter would be easy  the month() function gives you that.
HTH

Re: How to calculate the number of months between two dates
Rekha Harish Mar 11, 2015 8:24 AM (in response to Friedrich Hofmann)Hi,
I just want to consider the Month irrespective of the date.


Re: How to calculate the number of months between two dates
Manish Kachhia Mar 11, 2015 8:54 AM (in response to Rekha Harish )Test:
Load
*,
Date(MonthStart(From_dt),'MMM YYYY') as FromMonthStart,
Month(From_dt) as FromMonth,
Month(To_dt) as ToMonth,
(Month(To_dt)  Month(From_dt)+1) as IternationNumber;
Load
AutoNumber(From_dt & To_dt) as ID,
Date(MonthStart(From_dt)) as From_dt,
MonthEnd(To_dt) as To_dt,
Value
Inline
[
From_dt, To_dt, Value
1/1/2015, 1/3/2015, 3000
14/1/2015, 20/10/2015, 20000
7/3/2015, 12/5/2015, 6000
10/2/2015, 10/11/2015, 9000
];
Final:
Load
ID,
Date(AddMonths(Date#(FromMonthStart, 'MMM YYYY'),IterNo()1),'MMM YYYY') as FromMonthStart,
Value/IternationNumber as Value
Resident Test
While FromMonth + IterNo()  1 <= Month(To_dt);
Drop Table Test;

Re: How to calculate the number of months between two dates
Rekha Harish Mar 12, 2015 1:28 AM (in response to Manish Kachhia )Thank you Manish.


Re: How to calculate the number of months between two dates
Massimo Grossi Mar 11, 2015 7:10 PM (in response to Rekha Harish )a:
load
*,
year(To_dt)*12+month(To_dt)  (year(From_dt)*12+month(From_dt)) +1 as MonthDiff
inline [
From_dt , To_dt, Value
1/1/2015, 1/3/2015 ,3000
14/1/2015, 20/10/2015 ,20000
7/3/2015, 12/5/2015 ,6000
10/2/2015, 10/11/2015 ,9000
];
b:
load
From_dt , To_dt, Value/MonthDiff as NewValue,
AddMonths(MonthStart(From_dt), IterNo()1)
Resident a
While AddMonths(MonthStart(From_dt), IterNo()1) <= MonthStart(To_dt) ;
DROP Table a;