need help to write scripts for calculating the duration (days and hours)

[#1]

Hello,

I am beginner and  lost for the calculation of duration time and hours... I need some help for an important job!

I will send a champagna bottle form France to the guys who help me to find a solution

In the Interaction table, I've the field interaction ID (For example N°565), statut (open-idle), Begin date (01/01/2012 8:35:45) and end date (02/02/2012 16:24:54).

I want to calculate the duration between the begin date and the end date, but with teses rules :

- opening hours of service (from 8:30:00 to 18:30:00, Monsday to Friday)

- take care about halidays days

How i have to manage calendar for that?

The result will be a number of days and hours.

exemple 1 :

Open day is sunday fisrt january 2012 at 8:35:45

- if end date is monday 2 january 2012 at 15:30:00, the duration starts the monday at 8:30:00 (sunday is out the opening hours of service) and the result will be 7 hours.

- if end date is monday 9 january 2012 at 15:30:00, the duration starts the monday 2 juanuary at 8:30:00, saturday and sunday will not be in the calculation  and the result will be 5 days and 7 hours.

exemple 2 :

Open day is sunday fisrt january 2012 at 18:35:45

- if end date is monday 2 january 2012 at 19:30:00, the duration starts the monday at 8:30:00 and ends at 18:30:00 (sunday is out the opening hours of service) and the result will be 1 day (the duration est egal to the openinf duration of the day = 10 hours).

Exeption :

if the end day is not fill in the data base, the calculation may be changed like that :

if the statut is closed, the end date is egal to begin date, so the duration is null

if the statut is different to closed, the end date is the date of the last refreshement (reloading) to the database

My probleme is I don't know how to use calendar and I don't know SQL. I think I have to creat a table for the opening hours, and a another for the holidays... If you need more informations for my problem, don't hesitate to contact me

thank you for your help

Don't let me in the dark 

stephane

[swuehl · Accepted solution]

Stephane,

there are several approaches to this problem and AFAIR, this has been a topic here recently, so you should find some other solutions by searching the forum.

You can use the QV function networkdays() to get the number of working days (you can also pass a list of holidays as argument).

Then it's essentially something like:

Let vStartTime = num(maketime(8,30)); //8:30

Let vCloseTime = num(maketime(18,30)); // 18:30

Let vFullDay = num(maketime(10)); // 10 hours - full day

HolidayTable:

LOAD chr(39) & concat(distinct Date,chr(39)&','&chr(39) ) & chr(39) as HolidayList;

LOAD * INLINE [

Date, Holiday

'06.04.2012', 'Good Friday'

'09.04.2012', 'Easter Monday'

'01.05.2012', '1st of May'

'17.05.2012', 'Holiday'

];

//'01.01.2012', 'New Year'

//'04.01.2012', Test

//'06.01.2012', Epiphany

Let vHolidayList = peek('HolidayList',0,'HolidayTable');

INPUT:

LOAD recno() as RecID, InteractionID, Statut, BeginDate, if(isNum(EndDate), EndDate, if(Statut = 'closed', BeginDate, now(0))) as EndDate INLINE [

InteractionID, Statut, BeginDate, EndDate

565, closed, 1.1.2012 8:35:45, 2.2.2012 16:24:54

565, closed, 1.1.2012 8:35:45, 2.1.2012 15:30:00

565, closed, 1.1.2012 8:35:45, 9.1.2012 15:30:00

565, closed, 1.1.2012 18:35:45, 2.1.2012 19:30:00

565, closed, 1.1.2012 8:35:45,

565, idle, 1.1.2012 8:35:45,

];

RESULT:

LOAD *, floor(div(rangesum(HoursWorked1, HoursWorked2),$(vFullDay))) as DaysWorked

, interval(fmod(rangesum(HoursWorked1, HoursWorked2),$(vFullDay)),'hh:mm') as HoursWorked

;

LOAD RecID,

     EndDate,

     BeginDate,

     InteractionID,

     Statut,

// calculate total hours worked excluding the Start Day and the Close Day    

NetWorkDays(BeginDate+1,EndDate-1,$(vHolidayList) ) * $(vFullDay) as HoursWorked1,

//calculate the hours worked for the start and the end date

if(daystart(BeginDate) = daystart(EndDate),

if (networkdays(EndDate,EndDate,$(vHolidayList) ), rangemin(frac(EndDate), $(vCloseTime)) - rangemax(frac(BeginDate), $(vStartTime)), 0),

rangesum(if(networkdays(BeginDate,BeginDate, $(vHolidayList)), $(vCloseTime)-rangemax(frac(BeginDate),$(vStartTime))), if(networkdays(EndDate,EndDate, $(vHolidayList)), rangemin(frac(EndDate),$(vCloseTime))-$(vStartTime) ))

) as HoursWorked2

Resident INPUT;

drop table INPUT;

See also attached.

Hope this helps,

Stefan

[#3]

Thank you swuehl,

Your method seems to be very good, but my level is not enough good to understand everything in the right way

Could you take fews minutes to put it in the qvx doc by the below link?

http://www.hist-europe.fr/liens/GCE_RS.qvx

You will see ine the script page the comment lines where i need to insert your method.

You can write me a mail for giving me information in order i can send you champagne

luohgan@gmail.com

thank you for your very fast answer, you are amazing witj the duration calcul !

stephane

[swuehl]

Would be helpful if you could also post the excel file you are loading (with real or mock up data), thus allowing me to reload.

I've already noticed two minor issues (a potential one with the number format of the variables, in more detail with the decimal separator, the second one with using field names that are not known in the current load context. If you create a field like HoursWorked1 in a load, you can't reference this new field in the same load context. Use a subsequent load (which you commented out in your sample file), or use the full field definition instead the field name (which makes it hard to read though) ).

Regards,

Stefan

[#5]

swuehl,

here is the excel data : http://www.hist-europe.fr/liens/data.xlsx

regards

Stéphane

[#6]

swuehl,

I've found a solution.This is not the simpliest way but i can now calculate the time and hour between two date.

thankx to you to give me all informations for cathing my goal

this is what i've done :

origine:

LOAD categorie,

     id as N_mantis,

     1 as compteur,

     version,

     severite,     

     date_changement ,

     Year(date_changement ) as Année,

     Month(date_changement ) as Mois,

     Week (date_changement ) as Semaine,

     Day (date_changement ) as Jour,

     Hour (date_changement ) as theures,

     Minute (date_changement ) as minutes,

     Hour (date_changement )as heure,

     if ( NetWorkDays(date_changement,date_changement,$(vHolidayList) ) >=1, if ( Hour (date_changement ) + ((Minute (date_changement )/60))<8.5,1,

     if ( Hour (date_changement ) + ((Minute (date_changement )/60))>= 18.5,0, ((18.5 - (Hour (date_changement ) + ((Minute (date_changement )/60))))/10)) ),0) as tempsdebut,

     WeekDay (date_changement ) as JourSemaine,

     date(monthstart(date_changement), 'MMM-YYYY') AS MoisAnnée,

  date(Daystart(date_changement), 'DD-MMM-YYYY') as AnnéeMoisJour,

  dual(week(date_changement)&'-'&WeekYear(date_changement), WeekStart(date_changement)) as SemaineAnnée, 

      if(Prioritaire='Basse', dual('1-Basse', 1),

    if(Prioritaire ='Normale', dual('2-Normale', 2),

       if(Prioritaire ='Elevée', dual('3-Elevée', 3)

         )

      )

   ) as Prioritaire,

     [Etat en cours],

     Date_fin,

      if (NetWorkDays(Date_fin,Date_fin,$(vHolidayList) ) >=1, if ( Hour (Date_fin ) + ((Minute (Date_fin )/60))<8.5,0,

    if (Hour (Date_fin ) + ((Minute (Date_fin )/60))>= 18.5,1, ((((Hour (Date_fin ) + (Minute (Date_fin )/60))-8.5)) /10))),0) as Tempsfin,

     NetWorkDays(date_changement,Date_fin,$(vHolidayList)) as jourtravail,

    // sum (Tempsfin + tempsdebut + jourtravail) as durée_travail,

     Statut



FROM



(ooxml, embedded labels, table is Données);

But i have a probleme with the last field (i've put  itin comment), when i reload datas, the system told me he didin't find Tempsfin... may be i didn't write it in the right way....

[swuehl]

That's due to the issue I mentioned before, you can't reference an alias name in the same load context, so you need e.g. a preceding load.

I noticed that you have posted another thread with this issue and also got an answer there, so  -- are all issues resolved now, regarding this thread?

[#8]

Thank swuehl for your help.

this issue is over now, thanks to you

I put comment in the other thread

stephane