Create month from two date columns

[narender123]

Hi Team,

I have a table like :

employee_id              start_date                    end_date

1                                  01/01/2015                    05/03/2015

2                                  17/03/2015              25/04/2015

3                        15/03/2015              018/04/2015

- - - -  - - - - - -                - - - - - -  -                    - - - - -  -  -

I want to count "employee_id" based on the month between start_date and end_date.So how i can make a month calender started from "start_date"   &

ended to "end_date"????

As shown in above table the month should be jan,feb,mar for employee_id=1.

if i will select jan or feb or mar then it will only select employee_id=1.How i can create month calender that work in such way????

Thanks in advance.

Narender

[MK_QSL]

Temp:

Load * Inline

[

  employee_id, start_date, end_date

  1, 01/01/2015, 05/03/2015

  2, 17/03/2015, 25/04/2015

  3, 15/03/2015, 18/04/2015

];

NoConcatenate

Final:

Load

  employee_id,

  start_date,

  end_date,

  Month(AddMonths(start_date, IterNo() - 1)) as Month

Resident Temp

While Month(AddMonths(start_date, IterNo() - 1)) <= Month(end_date);

Drop Table Temp;

[#3]

Hi

check this file

[narender123]

Hi Manish,

Thanks for your reply.

Its working but i have large collection of data record.So its taking too much time to reload.Is there any other way?

[MK_QSL]

Temp:

Load

  employee_id,

  start_date,

  If(Len(Trim(end_date))=0 or IsNull(end_date), Date(Today()),end_date) as end_date

Inline

[

  employee_id, start_date, end_date

  1, 01/01/2015, 05/03/2015

  2, 17/03/2015, 25/04/2015

  3, 15/03/2015, 18/04/2015

  4, 01/01/2015,

];

MinMaxDate:

Load Min(start_date) as MinDate, Max(end_date) as MaxDate Resident Temp;

Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

Final:

Load *, Month(Date) as Month;

Load

  $(vMinDate) + IterNo() - 1 as Date

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Inner Join IntervalMatch(Date)

Load start_date, end_date Resident Temp;

Join(Final) Load * Resident Temp;

Drop Table Temp;

[narender123]

Hi Manish,

Its showing wrong data as

count(employee_id) for jan month = 31 which is wrong.

it should show count= 2 for jan month.

Thanks,