employee shortages control

[MarcoARaymundo]

Hi!

I have a table like this:

Person	StartDate	EndDate
Ktrsa1	2016-01-01	2016-01-02
Ktrsa1	2016-01-05	2016-01-10

And a table calendar by dates.

I need create a sheet like this:

Any idea?

Tks

[sunny_talwar · Accepted solution]

You will need to use Interval Match to make this to work: IntervalMatch

See if the blog makes sense

Check the sample out:

Script:

Table:

LOAD * Inline [

Person, StartDate, EndDate

Ktrsa1, 2016-01-01, 2016-01-02

Ktrsa1, 2016-01-05, 2016-01-10

];

MinMax:

LOAD Min(StartDate) as MinDate,

  Max(EndDate) as MaxDate

Resident Table;

LET varMinDate = Peek('MinDate');

LET varMaxDate = Peek('MaxDate');

DROP Table MinMax;

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

TempCalendar: 

LOAD $(varMinDate) + Iterno()-1 As Num, 

     Date($(varMinDate) + IterNo() - 1) as TempDate 

     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load TempDate AS Date, 

     week(TempDate) As Week, 

     Year(TempDate) As Year, 

     Month(TempDate) As Month, 

     Day(TempDate) As Day, 

     YeartoDate(TempDate)*-1 as CurYTDFlag, 

     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

     WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

Left Join(Table)

IntervalMatch(Date)

LOAD StartDate,

  EndDate

Resident Table;

Output:

[MarcoARaymundo]

Tks Sunny T.