Monday, November 01, 2010

Brio Working hour calculation

Brio query does not even have day of week not to mention networkingdays. The following is my own code to do the calculation. The calculation actually calculate the working hours between the two date excluding the off office hours and week ends. Unfortunately it is too much work to do a holiday exclusion.

Note that date5 is the earlier date and date6 is the latter date.

var date5 = CreateDate
var date6 = CreateDate2
var date1 = ToDate(ToChar(date5,"dd/mm/yyyy"));
var date2 = Nvl(date6,Sysdate());
var date3 = ToDate(ToChar(date2,"dd/mm/yyyy"));
var datecnt = date3-date1;
if (datecnt > 0){
var date4=date1
var offset=0
if (datecnt > 1 ){
for (x=1;x<datecnt;x++){
date4=date4+1
var nmonday=NextDay(date4,"Sunday");
var wkday = 7-(nmonday-date4);
if (wkday == 6) {
offset = offset + 0.25
} else {
if (wkday !=0) {offset = offset + 0.5}
}
}
}
var nmonday=NextDay(date1,"Sunday");
var wkday = 7-(nmonday-date1);
dateb=date5-date1
if (dateb < 9/24) {date5 = date1 +9/24}
if (dateb > 21/24) {date5 = date1 + 21/24}
if (dateb > 15/24 && wkday ==6) {date5 = date1 + 15/24}
if (wkday == 0){ datea = 0 } else {
if (wkday == 6) {datea=(date1+15/24)-date5} else {datea =(date1+21/24)-date5}
}
offset = offset+datea
var dateb = date6-date3
if (dateb < 9/24) {dateb = 9}
if (dateb > 21/24) {dateb = 21}
dateb= dateb-9/24;
(offset+dateb)*24*60
} else {(date6-date5)*24*60}

No comments:

Post a Comment