How to get all months that fall between two dates.

[#1]

How to get all months that fall between two date/Month.

For eg. in below screen shot EMP Id =1, is associated with two month 1 (Jan) and 6 (June).

Desired Output should be like :

TEST2.EmpId	TEST2.Month1	TEST2.Monttname_final
1	1	Jan
1	2	Feb
1	3	Mar
1	4	April
1	5	May
1	6	Jun
2	4	Apr
3	2	Feb
3	3	March
3	4	April
3	5	May

Below is the code i have used so far, but stuck with the month range part.Kinldy help

MonthDetails:

LOAD * INLINE [

    Month, MonthName

    1, Jan

    2, Feb

    3, Mar

    4, Apr

    5, May

    6, Jun

    7, Jul

    8, Aug

    9, Sep

    10, Oct

    11, Nov

    12, Dec

];

MLoad:

Mapping Load Month,MonthName Resident MonthDetails;

TEST:

LOAD *,SubField(WDate,'/',2) as 'Month1',

ApplyMap('MLoad',SubField(WDate,'/',2)) as Monttname_final

INLINE [

    Emp Id, Dept, WDate

    1, Admin, 1/1

    1, Store, 3/6

    2, HR, 2/4

    3, Infra, 15/2

    3, Store, 5/5

];

[daveamz]

Hi Niket,

Try the WHILE cause, something like:

T1:
LOAD * INLINE [
Emp Id, Month#, Month
1,1,Jan
1,6,Jun
2,4,Apr
3,2,Feb
3,5,May
];

T2:
LOAD [Emp Id],
      Min(Month#) AS MinMonth#,
      Max(Month#) AS MaxMonth#
RESIDENT T1
GROUP BY [Emp Id]
;

DROP TABLE T1;

T3:
LOAD [Emp Id],
      MinMonth#+IterNo()-1 AS Month#
RESIDENT T2
WHILE MinMonth#+IterNo()-1

Regards,

David

[Kushal_Chawda]

try this

TEST:

LOAD *,SubField(WDate,'/',2) as MonthNum1

INLINE [

    Emp Id, Dept, WDate

    1, Admin, 1/1

    1, Store, 3/6

    2, HR, 2/4

    3, Infra, 15/2

    3, Store, 5/5 ];

Link1:

LOAD [Emp Id],

            min(MonthNum1) as MinMonthNum,

            max(MonthNum1) as MaxMonthNum

Resident TEST

Group by [Emp Id];

Link:

LOAD  *,

          MinMonthNum+IterNo()-1 as MonthNum,

          month(makedate(Year(Today()), MinMonthNum+IterNo()-1,1)) as MonthName

Resident Link1

While MinMonthNum+IterNo()-1<=MaxMonthNum;

DROP Table Link1;

DROP Fields MinMonthNum,MaxMonthNum,MonthNum1;

[#4]

Thanks Kushal, this is what I exactly looking for..Cheers!!!

[Kushal_Chawda]

no problem bro.