Thank you for your response. I have already used yours posts as reference to get the solution, and it almost works fine. But I am a little perplexed to see that the interval is still not exactly as desired (or maybe I'm missing something here since there is difference of a few hours between the actual and the desired interval)
Since, you coined this solution, could you kindly take a look at the attached sample to see what's missing/wrong with the code?
Would appreciate your cooperation.
first, please check the string format codes for date and time, MM is denoting month, not minute, so you must use something like
timestamp(close_date,'MM/DD/YYYY hh:mm:ss') as close_date,
timestamp(open_date,'MM/DD/YYYY hh:mm:ss') as open_date,
to get a proper time part (it's confusing to see the month as minutes in your sample app ;-).
Your time diiference is caused by your custom weekend (Thu & Fri) limitation, you need to use
where (WeekDay(Date)<>3 and WeekDay(Date)<>4) and not match(Date,$(vHol))
Please check the WeekDay() function, which is returning a number in the range from 0 (Mon) to 6 (Sun).
Using above changed where clause, I get 23:02:24 for your sample (the time worked on Sat is appr. 5, not 6 hours).
Hope this helps,
I essentially have the exact same setup as the original post. Bulk of relevant script below. However, because the left join has a where weekday(date)<5, if the date is a weekend, the left join results in a blank value for that row. So ultimately i would like to be able to modify this such that the working hours and days are set within the initial TMP table such that for day 5 & 6 working hourse are 00:00:00 - 00:00:00.
left join (jira_issue)
resident issue_priority ;
/// set variable for holidays
set vHol = '41459,41094';
daystart(priority_set_date)+iterno()-1 as Date,
if(iterno()=1, rangemin(rangemax(frac(priority_set_date),maketime(8)),maketime(18)), maketime(8)) as Start,
if(daystart(priority_set_date)+iterno()-1=daystart(closed_datetime), rangemax(maketime(8),rangemin(frac(closed_datetime),maketime(18))),Maketime(18)) as End
while daystart(closed_datetime) >= daystart(priority_set_date)+iterno()-1;
left join (jira_issue)
interval(sum(End-Start), 'hh:mm:ss') as Priority_duration
Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by %KEY_ISSUE;
drop table TMP;
drop table issue_priority;