1 Reply Latest reply on Nov 21, 2016 9:28 AM by Dan Huff

    HOW TO UPDATE ALL RECORDS AS OVERBOOKED

    praveen reddy

      Hi

       

      We have a scenario to calculate the duration availability of a technician between one project to the other, where in some technicians are assigned to the projects properly in series (after completion of previous assignment) while some are not properly assigned (there is overlap in assignment) as below.

       

      We are calculating duration Based on Previous assignment end date and Next assignment start date:

       

      CALCUALTION USED FOR DURATION -- attr([End Date])<[Start Next Project] then [Start Next Project]-attr([End Date]) END

       

      Technician

      Project

      Start Date

      End Date

      Duration

      _DUTRONQUAY, Cédric

      CHINA SHIPPING

      9/2/2016 0:00

      9/15/2016 0:00

      5

      INDISPONIBLE

      9/20/2016 0:00

      9/30/2016 0:00

      10

      FREEPORT MASSA

      10/10/2016 0:00

      10/28/2016 0:00

      1

      _

      PACIFICA

      10/29/2016 0:00

      10/30/2016 0:00

      null

      ALIOUANE Omar

      Astreinte Wind; FY2016; FW35

      8/29/2016 7:00

      9/5/2016 6:59

      -5

      C-006779-1-2;EDF - Le Carnet Offshore Wind Farm;

      8/31/2016 0:00

      9/1/2016 0:00

      4

      Astreinte Système client sous contrat;FY2016;FW36

      9/5/2016 7:00

      9/12/2016 7:00

      0

      C-011823;GRTgaz - Cherré Compressor station;

      9/12/2016 0:00

      9/14/2016 0:00

      4

      C-011561;Total Infrastructures Gaz France ( TIGF ) - Lussagnet

      9/18/2016 0:00

      9/21/2016 0:00

      5

      C-006779-2-1;BELWIND OOSTENDE;

      9/26/2016 0:00

      9/27/2016 0:00

      6

      C-006779-2-1;BELWIND OOSTENDE;

      10/3/2016 0:00

      10/7/2016 0:00

      3

      C-006779-2-1;BELWIND OOSTENDE;

      10/10/2016 0:00

      10/13/2016 0:00

      4

      C-006779-6-5;BELWIND OOSTENDE;

      10/17/2016 0:00

      10/21/2016 0:00

      3

      Astreinte Syst. client sous contrat;FY2016;FW43

      10/24/2016 7:00

      11/2/2016 8:00

      -2

      Congés

      10/31/2016 0:00

      10/31/2016 0:00

      1

      Jour Férié

      11/1/2016 0:00

      11/1/2016 0:00

      10

      Jour Férié

      11/11/2016 0:00

      11/11/2016 0:00

      3

      C-011302;Air Liquide - Severstal Industrial Gases Plant;

      11/14/2016 0:00

      11/19/2016 0:00

      null

       

       

      We are looking for a solution to have a flag field to identify the technicians who are overbooked and who are normally assigned as below.

       

      Currently we are using below calculation to achieve it, but it is giving overbooked flag only to the projects where there is project overlap or negative duration:

       

      if attr([Technician])=lookup(attr([Technician]),1) and

       

      attr(End Date])>[Start Next Project] then "Overbooked" ELSE

      "Available" END

       

      By  above condition we are able to achieve like below shown in screen 1----  but we are trying to update all the records of that techinician as over booked shown in screen -2 ‘

       

      SCREEN-1

       

      Technician

      Project

      Start Date

      End Date

      Availabity checker

      Flag

      _DUTRONQUAY, Cédric

      CHINA SHIPPING

      9/2/2016 0:00

      9/15/2016 0:00

      5

      Available

      INDISPONIBLE

      9/20/2016 0:00

      9/30/2016 0:00

      10

      Available

      FREEPORT MASSA

      10/10/2016 0:00

      10/28/2016 0:00

      1

      Available

      PACIFICA

      10/29/2016 0:00

      10/30/2016 0:00

      null

      Available

      ALIOUANE Omar

      Astreinte Wind; FY2016; FW35

      8/29/2016 7:00

      9/5/2016 6:59

      -5

      Over Booked

      C-006779-1-2;EDF - Le Carnet Offshore Wind Farm;

      8/31/2016 0:00

      9/1/2016 0:00

      4

      Available

      Astreinte Système client sous contrat;FY2016;FW36

      9/5/2016 7:00

      9/12/2016 7:00

      0

      Available

      C-011823;GRTgaz - Cherré Compressor station;

      9/12/2016 0:00

      9/14/2016 0:00

      4

      Available

       

      SCREEN-2 – Expecting RESULTS

       

      Technician

      Project

      Start Date

      End Date

      Availabity checker

      Flag

      _DUTRONQUAY, Cédric

      CHINA SHIPPING

      9/2/2016 0:00

      9/15/2016 0:00

      5

      Available

      INDISPONIBLE

      9/20/2016 0:00

      9/30/2016 0:00

      10

      Available

      FREEPORT MASSA

      10/10/2016 0:00

      10/28/2016 0:00

      1

      Available

      PACIFICA

      10/29/2016 0:00

      10/30/2016 0:00

      null

      Available

      ALIOUANE Omar

      Astreinte Wind; FY2016; FW35

      8/29/2016 7:00

      9/5/2016 6:59

      -5

      Over Booked

      C-006779-1-2;EDF - Le Carnet Offshore Wind Farm;

      8/31/2016 0:00

      9/1/2016 0:00

      4

      Over Booked

      Astreinte Système client sous contrat;FY2016;FW36

      9/5/2016 7:00

      9/12/2016 7:00

      0

      Over Booked

      C-011823;GRTgaz - Cherré Compressor station;

      9/12/2016 0:00

      9/14/2016 0:00

      4

      Over Booked