Create Month from "between(start date and end_date)" fields.

[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

[Gysbert_Wassenaar]

Maybe something like this:

LOAD *, date(monthstart(start_date, IterNo()-1),'MMM') as month

WHILE monthstart(start_date, IterNo()-1) <= end_date;

LOAD

     employee_id,

     date#(start_date,'DD/MM/YYYY') as start_date,

     date#(end_date,'DD/MM/YYYY') as end_date

FROM ....    

[narender123]

Hi Gysbert,

Thanks for your reply.

I have already tried  this in past like:

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);

As my data is too much large this will take huge time which is not possible for me.I think you have suggested the same?? or it will also time consuming?

Pls suggest me .

Thanks.

[Gysbert_Wassenaar]

You don't explicitly make dates from the date string values in your inline load. That could lead to problems.

The while clause could lead to missing data. For example with start date 15/11/2014 and end date 28/03/2015. The month number of the end date is lower than the month of the start date. The while clause would always return false for this combination so no records would be added to the table.

[narender123]

Hi Gysbert,

Yes, you are right.For this, can i do this

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

Or any other way to sort out this?

Thanks

[narender123]

Hi Gysbert,

Can i use this:

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

pls assist.

Thanks

[Gysbert_Wassenaar]

You can use it. But I can't guarantee it will work. I don't know your application well enough to tell with certainty.

[CELAMBARASAN]

May be you could try the below approach

Employee:

LOAD *, MonthStart(start_date) AS MonthStart_StartDate,MonthEnd(end_date) AS MonthEnd_EndDate 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

];

Calendar:

LOAD

  Date(AddMonths(MinDate,IterNo()-1)) AS Date,

  MonthName(MinDate,IterNo()-1) AS MonthName

While Date(AddMonths(MinDate,IterNo()-1)) <= MaxDate;

LOAD

  MonthStart((Min(MonthStart_StartDate))) AS MinDate,

  MonthEnd(Max(MonthEnd_EndDate)) AS MaxDate

Resident Employee;

Left Join IntervalMatch(Date)

LOAD

  MonthStart_StartDate,MonthEnd_EndDate

Resident Employee;

[narender123]

Thanks Celambarasan Adhimulam.

But i didnt get your point.

Thanks.

[narender123]

Thanks Gysbert.