Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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