NetSuite: Custom Case Duration Search

This is a search that I've spent some time creating. Heads up, this is going to be a complicated search. The purpose of this search is to be able to track how many business hours have passed since a case has been created. Business hours are Mondays at 4AM through Fridays at 5PM.

floor(((abs(round(to_char(trunc({createddate},'DD'), 'J') - to_char(trunc({today},'DD'), 'J') - (trunc({createddate},'d')-trunc({today},'d'))/7*2 - (case when to_char({today}, 'DY') = 'SUN' then 1 else 0 end) - (case when to_char({createddate}, 'DY') = 'SAT' then 1 else 0 end), 2))-1)*(17-4)+(decode(sign(trunc({createddate},'DD')+17/24 - {createddate}), -1 , 0, trunc({createddate},'DD')+17/24 - {createddate}-(case when trunc({createddate},'DD')+4/24 - {createddate} > 0 then trunc({createddate},'DD')+4/24 - {createddate} else 0 end)))*24+(case when {today} > trunc({today},'DD')+17/24 then 17-4 else case when {today} > trunc({today},'DD')+4/24 then {today}-(trunc({today},'DD')+4/24) else 0 end end)*24)/(17-4)) || ' days ' || replace(to_char(trunc({today},'DD')+mod((abs(round(to_char(trunc({createddate},'DD'), 'J') - to_char(trunc({today},'DD'), 'J') - (trunc({createddate},'d')-trunc({today},'d'))/7*2 - (case when to_char({today}, 'DY') = 'SUN' then 1 else 0 end) - (case when to_char({createddate}, 'DY') = 'SAT' then 1 else 0 end), 2))-1)*(17-4)+(decode(sign(trunc({createddate},'DD')+17/24 - {createddate}), -1 , 0, trunc({createddate},'DD')+17/24 - {createddate}-(case when trunc({createddate},'DD')+4/24 - {createddate} > 0 then trunc({createddate},'DD')+4/24 - {createddate} else 0 end)))*24+(case when {today} > trunc({today},'DD')+17/24 then 17-4 else case when {today} > trunc({today},'DD')+4/24 then {today}-(trunc({today},'DD')+4/24) else 0 end end)*24,(17-4))/24,'hh24:mi'),':',' hours ') || ' minutes'

Work hours between createddate and today

This section gets the whole days first. The floor is explained later. Nonetheless, the formula gets the difference between the current date and the createddate minus 2 days for each weekend in between those dates. Then the formula checks if the current date is a Sunday, then if the createddate was on a Saturday (if so, the total is reduced by 1 day). The result is subtracted by one since the current date is not yet considered a whole day. Then the result is converted to work hours by multiplying the number of work hours per day.

Hours to add from creation day

This section calculates how many hours to add depending on what time the case was created. If it was created before the start of day, then the full number of work hours in a day is added. If the case was created during work hours, then the difference between the current date and the created date is added. If the case was created after work hours, 0 hours is added. The formula uses a nested decode, with a case when function in the main decode's else paramater. I must admit I should have written this better.

Hours to add from today

Similarly to the previous section, this section adds work hours depending on what time the search was run.

Conversion from work hours to work days

Speaks for itself. This also explains the use of floor (since the goal is to get a whole number of days).

Remainder handling

Pretty much the same formula is used to get hours and minutes. Except this time, we don't use floor. So the same formula goes through mod 13. The result, already converted into work hours, gets converted via to_char, into hh24:mi format.

The final result shows how many work hours converted to a 24-hour format. For instance, If you have 13 work hours from 4am-5pm, a case was created at 3am, and was viewed at 6pm the next day, then the result be 26 work hours, and would be displayed as "1 days 2 hours 00 minutes".