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


      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





      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








                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 ;






      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;