MonthName between Start Date and End Date

[engishfaque]

Dear All,

I'm working on listed below scenario, and trying to find Month Name from/between Start_Date and End_Date.

Employee_ID	Employee_Name	Start_Date	End_Date
1001	AAA	01-Jan-14
1002	BBB	27-Feb-15	03-Aug-15
1003	CCC	22-Jul-14	29-Jul-14
1004	DDD	13-Mar-15
1005	EEE	31-Jul-14	02-Aug-15

Required output:

if i select Employee_Name (BBB) then Month Field must show me name of the months means Feb, Mar, Apr, May, Jun, Jul and Aug,

because Start_Date month is Feb and End_Date month is Aug for Employee_Name (BBB).

Same for other Employee_Name.

Kind regards,

Ishfaque Ahmed

[MarcoWedel · Accepted solution]

Hi,

you could create a master calendar and link it to your table using the intervalmatch prefix.

One script to create the calendar could be:

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Start_Date) as MinDate, 
    Max(End_Date) as MaxDate 
Resident yourtable;

the link table could be created something like this:

tabDateLink:
Intervalmatch (Date)
LOAD Start_Date, End_Date
Resident yourtable;

hope this helps

regards

Marco

[engishfaque]

Dear Marco,

If you check that, Employee_ID = 1001 record. We have a Start_Date but not End_Date.

How to consider Today Date for End Date where there is no End Date in against of any record?

Kind regards,

Ishfaque Ahmed

[MarcoWedel]

Hi,

try with

Alt(End_Date,Today()) as End_Date

or

If(Len(End_Date),End_Date,Today()) as End_Date

or somthing like that.

regards

Marco