Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a below requirement.
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 |
For eg:when I select 1/1/2015 then I need to find the number of months from from_dt to to_dt then distribute the values equally for those months i.e I will consider from Jan to Mar as 3 months and have to display the output as below.
Jan 2015 Feb 2015 Mar 2015
1000 1000 1000
Note:Date is in dd/mm/yyyy format.
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;
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
Hi,
I just want to consider the Month irrespective of the date.
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;
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;
Thank you Manish.