First/Last available date of the month (in script)

[jhamard]

Hi everybody,

I've a calendar that contain some "available dates".

I'm looking for a way to calculate (in the script) for each dates :

1. the previous date
2. the first available date of the month
3. the last available date of the month

My calendar :

The expected result :

Example of script :

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

Calendar:
LOAD * INLINE [
    myDate
    05.01.2015
    06.01.2015
    10.01.2015
    15.01.2015
    27.01.2015
    03.02.2015
    10.02.2015
    12.02.2015
    23.02.2015
    01.03.2015
    02.03.2015
    03.03.2015
    04.03.2015
    31.03.2015
];

Join(Calendar)
LOAD *
  ,Previous(myDate) as previousDate
Resident Calendar
Order by myDate asc;

EXIT Script;

Any suggestion ?

Thanks in advance,

Julien

[sunny_talwar]

Try this:

Calendar:

LOAD *,

  Month(myDate) as Month;

LOAD * INLINE [

    myDate

    05.01.2015

    06.01.2015

    10.01.2015

    15.01.2015

    27.01.2015

    03.02.2015

    10.02.2015

    12.02.2015

    23.02.2015

    01.03.2015

    02.03.2015

    03.03.2015

    04.03.2015

    31.03.2015

];

Join(Calendar)

LOAD Month,

  Date(Min(myDate)) as firstOfMonth,

  Date(Max(myDate)) as lastOfMonth

Resident Calendar

Group By Month;

Join(Calendar)

LOAD *

  ,Previous(myDate) as previousDate

Resident Calendar

Order by myDate asc;

EXIT Script;

[rubenmarin]

Hi Julian, maybe there is a better way but this is an option:

Calendar:

LOAD Date(myDate) as myDate, Month(myDate) as myDateMonth INLINE [

    myDate

   ....[data]

];

//Retieving min and max by month (take care of years?)

map_first_last:

Mapping LOAD myDateMonth, Min(myDate)&'#'&Max(myDate)

Resident Calendar group by myDateMonth;

Join(Calendar)

LOAD *

  ,Previous(myDate) as previousDate

  ,date(Subfield(ApplyMap('map_first_last', Month(myDate)), '#', 1)) as firstDate

  ,date(Subfield(ApplyMap('map_first_last', Month(myDate)), '#', 2)) as lastDate

Resident Calendar

Order by myDate asc;

EXIT Script;

[jhamard · Accepted solution]

Thanks I had the same idea when driving back home 😉

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

Calendar:
LOAD * INLINE [
    myDate
    05.01.2015
    06.01.2015
    10.01.2015
    15.01.2015
    27.01.2015
    03.02.2015
    10.02.2015
    12.02.2015
    23.02.2015
    01.03.2015
    02.03.2015
    03.03.2015
    04.03.2015
    31.03.2015
];

Join(Calendar)
LOAD *
    ,Previous(myDate)                    as previousDate
    ,Year(myDate)&'.'&Month(myDate)        as YYYY.MM
Resident Calendar
Order by myDate asc;

Join(Calendar)
LOAD YYYY.MM
    ,Date(Min(myDate))                    as firstOfMonth
    ,Date(Max(myDate))                    as lastOfMonth
Resident Calendar
Group by YYYY.MM;

EXIT Script;