Business Hours Calculation

[rash_611]

Hi,

I have the logic of finding the business hours but need to calculate between many fields. so is their a way to write in a function and call each time for different fields... please help me.... how to write TMP codes in a function

code for business hours:

INPUT:

LOAD

      CallNo,
        timestamp(opendate) as calldate,
        timestamp(resolvedate) as ResolveDate

        timestamp(closedate) as closedate

from calldetails.qvd (qvd);

TMP:

LOAD

      CallNo,
        frac(calldate) as check,
        daystart(calldate)+iterno()-1 as Date,
        if(iterno()=1, rangemin(rangemax(frac(calldate),maketime(9,30)),maketime(17,30)), maketime(9,30)) as Start,
        if(WeekDay(daystart(calldate)+iterno()-1)='Sat', 'yes','no') as Check_sat,
        if(WeekDay(daystart(calldate)+iterno()-1)='Sat',

        if(daystart(calldate)+iterno()-1=daystart(ResolveDate),

              rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(14,30))),Maketime(14,30)),
               if(daystart(calldate)+iterno()-1=daystart(ResolveDate),

                    rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(17,30))),Maketime(17,30))) as End

Resident INPUT;

left join (INPUT)
LOAD
     CallNo,
     interval(sum(End-Start)) as Duration
Resident TMP where WeekDay(Date)<6 and not match(Date,$(vHol)) group by CallNo;

[rash_611]

can any one help me plz......

[jonathandienst]

Hi

It sound like you need a preceding load. Something like this (not syntax checked):

TMP:

LOAD *,

  If(Check_sat = 'yes',

  If(Date = daystart(ResolveDate),

  _satEnd, Maketime(14,30)),

  If(Date = daystart(ResolveDate),

  _othEnd, Maketime(17,30))

  ) As End

;

LOAD

  CallNo,

  frac(calldate) as check,

  daystart(calldate)+iterno()-1 as Date,

  if(iterno()=1, rangemin(rangemax(frac(calldate),maketime(9,30)),maketime(17,30)),           maketime(9,30)) as Start,

  if(WeekDay(daystart(calldate)+iterno()-1)='Sat', 'yes','no') as Check_sat,

  rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(14,30))) As _satEnd,

  rangemax(maketime(9,30),rangemin(frac(ResolveDate),maketime(17,30))) As _othEnd

Resident INPUT;

DROP Fields _satEnd, _othEnd;

The drop statement just clears up unneeded fields. You can omit that if you want to use these calcs again somewhere else.

HTH

Jonathan

[rash_611]

hi, thanks for the reply... but i need to calculate business hours for more than 5 fields so i want to reuse the code.. how do i do it...

[tresB]

Have you thought of using subroutines? Like

Sub YourSub(parameters)

....

End sub

Then recall it using CALL

[rash_611]

yes, i have used the subroutines but not able to like that my model how to do that....