I don't understand why your condition at the first condition if the resolvedDateTIme is not < the holidays and weekend, and createdDT is not > the holidays, then it is 60,And if the resolvedDT is not > targetDate, then it is 0???

Not sure I understand what you're asking here. But I was just trying to honor your original logic for [Busi_Hrs_Cr_Resl] vs. [Busi_Hrs_Targ_Resl]

I thought if the task was resolved prior to the target date then the business hours would be under [Busi_Hrs_Cr_Resl].

But if the resolved date was after the targetDate then the business hours would fall under [Busi_Hrs_Targ_Resl].

is the main expression that coordinates with the JOIN to calendar table. Only hours that fall between 8AM and 5PM will be included in the results. However, if a person starts between 8:00 and 9:00 AM OR a person quits between 4:00 and 5:00 PM then partial hours must be used. That is the logic within the WHEN parts of the CASE. If it is not those conditions then it must be a full hour - 60 minutes.

If you want to see the times hour by hour you can remove the GROUP BY, the Total_Work_Order, and comment out the blue portions of both expressions:

quote:EDIT 2:I updated the statement because there was an error in the main CASE expression that calculates business hours.I also incorporated your sample data below and the 7/2 holiday.as well as added the output to the statement - but anyone should be able to run it themselves

I'm sorry but I don't see how you get to your expected results.Here is what I am doing:

For both these column (Busi_Hrs_Cr_Resl and Busi_Hrs_Targ_Resl)I am summing up hours that are within 8-5 of business days. In no case is any holiday, weekend, or off work hours included.

The only difference between the columns is that:if ResolvedDate > TargetDate then Busi_Hrs_Targ_Resl has the business hoursif resolvedDate < TargetDate then Busi_Hrs_Cr_Resl has the business hours.

That is obviously different then what your expected results show. Would you please try to explain again what the logic should be for:Busi_Hrs_Targ_Resl

and what the logic should be for:Busi_Hrs_Cr_Resl

Is there any time when you want either non-work hours on work days, or weekend hours, or holiday hours included in your calculation?

One more question about your expected results:BusiHr_Cr_Resol: 40,21BusiHr_Tar_Resol: 39,36

Is that comma a decimal point? ie: same as 40.21 hours and 39.36 hours?

EDIT:I forgot to mention that the total hours that I'm starting from to get business hours is always the difference between CreatedDatetime and ResolvedDateTime.

is that: 4 hours 45 minutes + 32 hours 0 minutes + 2 hours 36 minutes.wouldn't that be: 39 hours 21 minutes?You seem to be combining fractions of an hour with hours:minutes. I was providing fractions of an hour based on some of our earlier posts.I can change it to Hours:Minutes if you want.

Also, I thought earlier you defined business hours as 8am-5pm but your samples seem to now be 8am-4pm (16:00). Which is it? if it is 8-5 then a work day is 9 hours unless you are subtracting for a lunch hour.

quote:If ResovledDT > TargetDate and Time between 8:00AM to 16:00PM, then

Ok - I've updated that same post on page 2 (again). This time to show hours:minutes. I also removed this column: [Busi_Hrs_Targ_Resl] from the output. Unfortunately, because you started out with a (sort of) simple requirement to sum business hours, but now have different columns contributing to total hours depending...This solution won't work for both hours calculations. At least not without some major tweaking and I've run out of time to help - at least for today.

You're welcome. Apologies to other users for this never ending thread...

Ok - using the same solution I simply added two derived tables: one for Created one for Target. Assuming an 8 hour work day (8-4) these results make sense to me. And I cut the seconds off your sample times just to avoid rounding confusion. I think you can forget about lunch 1/2 hours though - unless you define a bunch more rules like how many continuous hours someone has to work in in day to get a 1/2 hour off.