4 Replies Latest reply: Jan 29, 2018 8:42 AM by Sunny Talwar RSS

    Splitting a single date range row into many row according to working day and Holidays

    enrico camerin

      Dears,

      I have this Range table and I need to create a single row for each working day within the StartDate and EndDate.

      The amount have to be diveded by the number working day found in the Range.

       

      I have this script which work great in a normal calendar, but I'm not able to adapt it in order to get only the working day ( without Saturday, Sunday and  special days).

      thanks in Advance for your Help

      Enrico

       

      Script

       

      load * inline [

      cod, StartDate, EndDate, amount

      'a', '01-02-2018', '30-04-2018', 8000

      'b', '01-03-2018', '30-06-2018', 4000

      'c', '01-06-2018', '31-07-2018', 12000

      ];

       

       

       

      expanded:

      LOAD

       

                cod, iterno() as nr,

              

              date(RangeMax(StartDate, StartDate+  iterno()-1)) as xStartDate,

               date(RangeMin(EndDate, StartDate+ iterno()-1)) as xEndDate,

              amount as amount_expanded

       

      RESIDENT range

       

      WHILE (StartDate+ iterno()-1) <= EndDate ;

       

       

       

      work_package_period:

       

      load cod, max(nr)as nr_day

      Resident expanded

      group by cod;

       

       

      /// comment   nr_day is the use in report to dived the amount

      inner join (expanded)

      load cod,  nr_day

      resident work_package_period;

       

      drop table work_package_period;

       

       

       

      exit Script;