How to find days between two dates for each month of a year?

[imsushantjain]

Hi Guys,

We have data that currently exist in this format:

S.No	Start Date	End Date
1	15-Jan-2020	20-Feb-2020
2	10-Jan-2020	25-Mar-2020
3	15-Feb-2020	20-Jun-2020

My user is interested in looking at Number of Days between Start and End Date by Month for each Year. How can we achieve the below  output using formulas or any other approch:

S.No	Start Date	End Date	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
1	15-Jan-2020	20-Feb-2020	16	20
2	10-Jan-2020	25-Mar-2020	21	29	25
3	15-Feb-2020	20-Jun-2020		14	31	30	31	20

Thanks

Sushant

[Kushal_Chawda · Accepted solution]

@imsushantjain  try below

Data:
LOAD [S.No],
     [Start Date], 	
     [End Date],
     date([Start Date]+IterNo()-1) as Date ,
     date(monthstart([Start Date]+IterNo()-1),'MMM YYYY') as Month,
     RowNo()&num([Start Date]+IterNo()-1) as Key
FROM table
while [Start Date]+IterNo()-1<=[End Date];

Below is the pivot table view with expression  count(distinct Date)

If you don't want the Day level data in table the you can aggregate it to month level  and calculate days in load script to reduce the number of records. Script will look something like below

Data:
LOAD [S.No],
     [Start Date], 	
     [End Date],
     date([Start Date]+IterNo()-1) as Date ,
     date(monthstart([Start Date]+IterNo()-1),'MMM YYYY') as Month
FROM table
while [Start Date]+IterNo()-1<=[End Date];

Final:
load [S.No],
     [Start Date], 	
     [End Date],
     Month,
     count(distinct Date) as Days
resident Data
group by [S.No],
     [Start Date], 	
     [End Date],
     Month;

Now you can simply use sum(Days) in your expression

[imsushantjain]

Thank You @Kushal_Chawda , your solution works like a charm. 😀