3 Replies Latest reply: Jun 5, 2012 8:39 PM by Ismael Villegas II RSS

    Adjusting Timestamp to Next Work Day

    Ismael Villegas II

      QV Users,


      I'am having issues with adjusting a timestamp. I have pulling in information on the arrival and completion time for customer emails. I have included a flow chart for the logic. I thought i would type it but the chart is so small and simple that I thought it would be best. I have a hoilday table inlcluded in my script and I hope to use it to skip over company holidays.


      Any assistance would be much appreciated.


        • Adjusting Timestamp to Next Work Day
          Stefan Wühl

          I think you should be able to reuse some of the logic I suggested here for a slightly different problem.



          I think you just need the part that correct the timestamp for the next business day (up to the Tickets table in the referenced example).


          In case you can't open other's files:


          // Create Calendar, start with non weekend or holiday



          date(makedate(2012)+recno()-2) as Date

          AutoGenerate 201;



          // join Holiday calendar

          left join LOAD * INLINE [

          Date, Holiday

          '01.01.2012', 'New Year'

          '04.01.2012', Test

          '06.01.2012', Epiphany

          '06.04.2012', 'Good Friday'

          '09.04.2012', 'Easter Monday'

          '01.05.2012', '1st of May'

          '17.05.2012', 'Holiday'




          // Create some additional fields (date information and correction used later on)


          LOAD *, WeekDay(Date) as Weekday, Month(Date) as Month, Year(Date) as Year;

          Load *, rangesum(peek(Add1Day,rowno()-rangesum(Add1Day,1)),1,Add1Day) as Add2Days;

          load *, if(weekday(peek(Date))>=5 or not isnull(peek(Holiday)), rangesum(peek(Add1Day),1)) as Add1Day

          Resident TMPCalendar order by Date desc;



          drop table TMPCalendar;



          // Create final calendar, calculate correction for ticket creation (to add of creation is a weekend / holiday)


          load *, if(Weekday >=5 or not isnull(Holiday), peek(Add1Day)) as CorrectStartDay

          Resident TMPCalendar2 order by Date asc;



          drop table TMPCalendar2;



          // Create (or - in real setting - load) ticket creation timestamp, correct timestamp to next productive period, part I)


          LOAD *, daystart(TicketCreationCorr) as TicketCreationCorrDate;

          LOAD *, timestamp(if(hour(TicketCreation) < 9, daystart(TicketCreation)+interval#('09:00:00'), if(hour(TicketCreation)>16, daystart(TicketCreation)+1+Interval#('09:00:00'),TicketCreation))) as TicketCreationCorr;

          LOAD Timestamp(makedate(2012)+RAND()*200) as TicketCreation

          AutoGenerate 1000;



          Left join LOAD Date as TicketCreationCorrDate, CorrectStartDay as TicketCorrectStartDay Resident Calendar;



          // Create final ticket table: Correct timestamp to next productive period (add CorrectStartDay), part II


          LOAD *, timestamp(rangesum(TicketCreationCorr,TicketCorrectStartDay)) as TicketProductiveStart, date(rangesum(TicketCreationCorrDate,TicketCorrectStartDay)) as TicketProductiveStartDate resident TMPTickets;



          drop table TMPTickets;



          Left join LOAD Date as TicketProductiveStartDate, Add1Day as TicketProductiveAdd1Day, Add2Days as TicketProductiveAdd2Days Resident Calendar;



          // Calculate optimal end timestamp for productive periods, taking weekends / holidays into account


          LOAD *, timestamp(rangesum(daystart(TicketProductiveStart)+interval#('09:00:00'),interval#('12:00:00')-(interval#('17:00:00')-frac(TicketProductiveStart)),

          if(hour(TicketProductiveStart)>12,TicketProductiveAdd2Days-interval#('08:00:00'),TicketProductiveAdd1Day),1 )) as TicketProductiveEnd;

          LOAD TicketProductiveStartDate as Date, TicketProductiveAdd1Day, TicketProductiveAdd2Days, TicketProductiveStart, TicketCreation Resident Tickets;



          // drop unneeded fields and tables

          drop table Tickets;

          drop fields Add1Day, Add2Days, TicketProductiveAdd1Day, TicketProductiveAdd2Days,CorrectStartDay;


          Hope this helps,


          • Adjusting Timestamp to Next Work Day
            Adnan Rafiq

            Please view this link as well,

            It has almost same problem being faced.