Number of days in a month between two dates

[ruma_barman]

Hi All,

I need to calculate total number of days of the month corresponding to each loan:

Suppose ,

Input is 

Loan	Start Date	End Date
1	07-Jan-19	21-Jan-19
2	24-Dec-19	30-Jun-20
3	04-Nov-19	18-Feb-20
4	28-Dec-18	03-Jan-19
5	11-Feb-20	25-Feb-20

Output is : 

Dec-19	Nov-19
69	56

 How to calculate the output:

Calculate total number of days for each loan and add all

Loan	Dec-19	Remarks
1	0	Jan 19 comes before Dec 19 ,so total days is 0
2	7	Since loan starts on 24Dec ,so we have (31-24)=7 days till end of month
3	31	Since loan starts on 4Nov19 and Ends on 18Feb 20 ,whole Dec 19 is covered,so 31 is total number of days
4	31	Same as above
5	0	Since loan starts after Dec19, total number of days is 0
	69

Similarly for Nov 19 with the same logic

Loan	Nov-19
1	0
2	0
3	26
4	30
5	0
	56

Seeking your help.

Thanks and Regards,

Ruma

[tresesco]

Where are these Dec-19 and Nov-19 coming from? Do you maintain a separate master calendar? Could you share a sample app with all links?

[ruma_barman]

Hi Tresesco,

Please find the app attached.

Thanks and Regards,

Ruma

[ruma_barman]

Hi Tresesco,

Any luck?

Regards,

Ruma

[tresesco]

I would recommend this to be handled in the script using date customized association. Is that an option?

[ruma_barman]

Yes. Can that be done in the script?

[ruma_barman]

Can anyone help please?

Regards,

Ruma

[sunny_talwar · Accepted solution]

Script

A:
LOAD Loan,
	 Date#("Start Date",'DD-MMM-YY') as [Start Date],
	 Date#("End Date",'DD-MMM-YY') as [End Date];
LOAD * INLINE [
    Loan,	Start Date,	End Date
    1,	07-Jan-19,	21-Jan-19
    2,	24-Dec-19,	30-Jun-20
    3,	04-Nov-19,	18-Feb-20
    4,	28-Dec-18,	03-Jan-20
    5,	11-Feb-20,	25-Feb-20
];

B:
LOAD Loan,
	 "Start Date",
     "End Date",
     Date("Start Date" + IterNo()) as Date,
     Floor(Date("Start Date" + IterNo())) as Num,
     Date(MonthStart(Date("Start Date" + IterNo())), 'MMM-YYYY') as MonthYear
Resident A
While "Start Date" + IterNo() <= [End Date];

DROP Table A;

Note: I changed the End Date for Loan 4 from 03-Jan-19 to 03-Jan-20 because you mentioned that you will see Days for Loan 4 in Jan-19 and Dec-19.

I did this using While loop, but you can also do this using Interval Match where you Interval Match Start and End Date with your calendar object.