SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Calculate total working hours
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/13/2012 :  17:17:13  Show Profile  Reply with Quote
I combined your table and logic into my solution. Hopefully I didn't mess up your logic too much. But at least this should return rows without sql errors.

--Check holidays
if object_id('tempdb.dbo.#holidays')> 0 drop table #holidays
select CAST ('20120101' AS DATETIME) as [DatesOfHolidays],'New Years' AS HOLIDAYS
into #holidays
union all select '20120220', 'Family Day'
union all select '20120406', 'Good Friday'
union all select '20120409', 'Easter Monday'
union all select '20120525', 'Victoria Day'
union all select '20120701', 'Canada Day'
union all select '20120806', 'Heritage Day'
union all select '20120903', 'Labour Day'
union all select '20121008', 'ThanksGiving Day'
union all select '20121111', 'Remember Day'
union all select '20121225', 'Christmas Day'
union all select '20121226', 'Boxing Day'
union all select '20120702', ''

;with
	a as (select convert(bigint,0) as n union all select 0),
	b as (select 0 as n from a as a cross join a as b),
	c as (select 0 as n from b as a cross join b as b),
	d as (select 0 as n from c as a cross join c as b),
	nums as (select row_number() over (order by (select 1)) as num from d as a cross join d as b),
	hrs as (select dateadd(hour, -num, dateadd(day, datediff(day, -1, getdate()),0)) as hr from nums),
	calendar as (
		select hr
			,isBus = case
				when datepart(weekday,hr) between 2 and 6 --mon-fri assuming @@datefirst = 7
				--and datepart(hour, hr) between 8 and 16 --8:00-9:00AM through 4:00-5:00PM
				and datepart(hour, hr) between 8 and 15 --8:00-9:00AM through 3:00-4:00PM
				then 1
				else 0
				end
		from hrs)
	,task (TaskDurationInHrs, ParentID, AssignedToTeam, AssignedTo, CreatedDateTime, TargetDate, ResolvedDateTime, Bu_Hrs_Cr_Resl, Bu_Hrs_Tg_Resl)
as (
	select           0, 0, 'Telus-Telecom', 'BK', convert(datetime,'1/3/2012 11:15:55 AM'), convert(datetime, '1/3/2012 12:00:46 AM'), convert(datetime, '1/10/2012 10:36:49 AM'), 0.00, 25.03
	union all select 0, 0, 'Telus-Telecom', 'TC', convert(datetime,'1/2/2012 1:00:42 AM'), convert(datetime, '1/9/2012 8:00:00 AM'), convert(datetime, '1/9/2012 1:19:23 PM'), 0.00, 25.03
	union all select 0, 0, 'Telus-Telecom', 'JP', convert(datetime,'1/2/2012 1:00:44 AM'), convert(datetime, '1/9/2012 8:00:00 AM'), convert(datetime, '1/6/2012 2:46:32 PM'), 0.00, 25.03
	union all select 0, 0, 'Telus-Telecom', 'DG', convert(datetime,'6/29/2012 2:37:02 PM'), convert(datetime, '7/31/2012 12:00:00 AM'), convert(datetime, '7/3/2012 11:48:39 AM'), 0.00, 25.03
	union all select 0, 0, 'Telus-Telecom', 'PE', convert(datetime,'6/29/2012 9:24:27 AM'), convert(datetime, '7/6/2012 9:22:03 AM'), convert(datetime, '7/13/2012 4:34:33 PM'), 0.00, 25.03
	)
select t1.ParentID
	,t1.AssignedTo
	,t1.CreatedDateTime
	,t1.TargetDate
	,t1.ResolvedDateTime
	,t1.TaskDurationInHrs
	
	--For the Busi_Hrs_Cr_Resl , I don't care the ResolvedDate > TargetDate or not, but care the duration between 2 dates must not include holiday and weekend and if the holiday in Saturday or Sunday, then the next Monday don't count for business hour ).
	--Busi_Hrs_Cr_Resl = resolvedDate - CreatedDate
	,Busi_Hrs_Cr_Resl = 
		convert(varchar(10), convert(int, sum(
		case
		when c.hr < t1.createdDateTime then datediff(minute, c.hr, t1.createdDateTime) 
		when t1.ResolvedDateTime between c.hr and dateadd(hour, 1, c.hr) then datediff(minute, c.hr, t1.ResolvedDateTime) 
		else 60
		end
		) / 60))
		+ ':' +
		convert(varchar(10), convert(int, sum(
		case
		when c.hr < t1.createdDateTime then datediff(minute, c.hr, t1.createdDateTime) 
		when t1.ResolvedDateTime between c.hr and dateadd(hour, 1, c.hr) then datediff(minute, c.hr, t1.ResolvedDateTime) 
		else 60
		end
		) % 60))

		
	--Total Work Order 
	--calendar will mess up count(*) so use a logical key for task.
	,Total_Work_Order = count(distinct convert(varchar(500), t1.AssignedTo)
		+ convert(char(23),t1.CreatedDateTime,121) 
		+ convert(char(23),t1.TargetDate,121) 
		+ convert(char(23),t1.ResolvedDateTime,121))
	
from	task t1
inner join calendar c
	on  (c.hr > t1.createdDateTime or datediff(minute, c.hr, t1.createdDateTime) < 60)
	and (t1.ResolvedDateTime > c.hr or datediff(minute, t1.ResolvedDateTime, c.hr) < 60)
	and c.isBus = 1
left outer join #holidays hol
	on datediff(day, hol.[DatesOfHolidays], c.hr) = 0
	and c.isBus = 1
where	hol.[DatesOfHolidays] is null
and	t1.createddatetime between '2012-01-01 00:00:00.000' and getdate()
and	(t1.AssignedToTeam = 'Telus-Telecom' or t1.AssignedToTeam = 'Telus-AOS')
and	t1.assignedTo <> ''
and	t1.AssignedTo is not null

group by t1.ParentID
	,t1.AssignedTo
	,t1.CreatedDateTime
	,t1.TargetDate
	,t1.ResolvedDateTime
	,t1.TaskDurationInHrs

OUTPUT:

ParentID    AssignedTo CreatedDateTime         TargetDate              ResolvedDateTime        TaskDurationInHrs Busi_Hrs_Cr_Resl      Total_Work_Order
----------- ---------- ----------------------- ----------------------- ----------------------- ----------------- --------------------- ----------------
0           BK         2012-01-03 11:15:55.000 2012-01-03 00:00:46.000 2012-01-10 10:36:49.000 0                 39:51                 1
0           DG         2012-06-29 14:37:02.000 2012-07-31 00:00:00.000 2012-07-03 11:48:39.000 0                 6:25                  1
0           JP         2012-01-02 01:00:44.000 2012-01-09 08:00:00.000 2012-01-06 14:46:32.000 0                 39:46                 1
0           PE         2012-06-29 09:24:27.000 2012-07-06 09:22:03.000 2012-07-13 16:34:33.000 0                 78:24                 1
0           TC         2012-01-02 01:00:42.000 2012-01-09 08:00:00.000 2012-01-09 13:19:23.000 0                 46:19                 1



EDIT:
I removed this because calendar.isBus should take care of it:
and DateDiff(hh,0,t1.createdDateTime)%24 between 8 and 16
and DateDiff(hh,0,t1.ResolvedDateTime)%24 between 8 and 16

and remember that if you make calendar a permanent table you can set the isBus flag to 0 for the holidays which will allow you to get rid of the #holiday table.

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.

EDIT 3:
updated again based on lastest posts

Be One with the Optimizer
TG

Edited by - TG on 07/16/2012 18:18:10
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/13/2012 :  18:19:30  Show Profile  Reply with Quote
if you need help please post some sample data for [task]. (include executable DDL/DML so I can just load it up). And the expected calculated output based on your sample data.

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  11:26:52  Show Profile  Reply with Quote
quote:
Originally posted by JadeV

Good morning TG,

My example data is:
AssignedTo CreatedDT TargetDate ResolvedDT Bu_Hrs_Cr_Resl Bu_Hrs_Tg_Resl
GH 2012-01-25 12:56:50.050 2012-01-30 00:00:00.000 2012-01-30 13:36:36.617 0.00 25.03
BK 2012-02-03 10:50:19.530 2012-02-06 11:00:23.000 2012-02-07 15:04:48.290 0.00 11.25
BK 2012-01-10 08:55:46.597 2012-12-06 12:28:09.473 2012-01-10 15:52:34.797 0.00 6.35
MM 2012-01-02 01:00:45.180 2012-01-09 08:00:00.000 2012-01-03 07:00:34.847 9.00 0.00
Please see my data example, and check. I want to have both hour working calculate for 2 cases.
The Bu-Hrs_Cr-Resol = 0 when ResolvedDT before TargetDate.
THe Bu-Hrs_Targ_Resol = 0 when TargetDate before ResolvedDT.

The MM person started work in 01:00, before 8:00 am, but it still counted.

THe



Good morning!
The reason MM has work hours because their hours are 1AM (Jan2) to 7 AM (Jan3) 30 actual hours

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  11:42:48  Show Profile  Reply with Quote
quote:
Originally posted by JadeV

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].

this expression:
		case
		when c.hr < t1.createdDateTime then datediff(minute, t1.createdDateTime, dateadd(hour, 1, c.hr))
		when dateadd(hour, 1, c.hr) > t1.ResolvedDateTime then datediff(minute, t1.ResolvedDateTime, dateadd(hour, 1, c.hr)) 
		else 60
		end

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:

	,Busi_Hrs_Cr_Resl = --convert(decimal(6,2), sum( 
	   case 
	   when t1.resolvedDateTime < t1.targetDate then
		case
		when c.hr < t1.createdDateTime then datediff(minute, t1.createdDateTime, dateadd(hour, 1, c.hr))
		when dateadd(hour, 1, c.hr) > t1.ResolvedDateTime then datediff(minute, t1.ResolvedDateTime, dateadd(hour, 1, c.hr)) 
		else 60
		end
	   else 0
	   end
--		) / 60.0)


Be One with the Optimizer
TG

Edited by - TG on 07/16/2012 11:48:06
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  12:19:41  Show Profile  Reply with Quote
>>but 1AM to 7:59 AM is not office hours. I don't count it.
1:00am (JAN 2) to 7:00am (JAN 2) is not business hours
but:
1:00am (JAN 2) to 7:00am (JAN 3) has business hours. all of hours between 8:00 am Jan 2 and 5:00 pm Jan 2.
It doesn't count hours between 5:00pm Jan 2 and 7:00am Jan3.

Regarding your other question - still not sure what you're askin. please do as I originally asked and post your expected output based on the sample data.

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  12:46:25  Show Profile  Reply with Quote
[code ] my formatted code [/code ]

just remove the trailing space before the close bracket ]

Be One with the Optimizer
TG
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/16/2012 :  14:05:47  Show Profile  Reply with Quote
HI TG,
I checked and I want to do like this
1) business_hours = resolvedDT - createdDT when createdDT = ResolvedDT
2) business_hours = resolvedDT - createdDT - (holidays + weekends) when createdDT < holidays and weekend < resolvedDT
3) business hours = resolvedDT - targetDate -(holidays + weekends) when targetDT < holidays and weekend < resolvedDT

My database example:
1) TC
CreatedDT: 1/23/2012 6:44:30 AM
TargetDate: 12/6/2011 12:28:09 PM
ResovledDT: 1/23/2012 11:39:35 AM

2) TC
CreatedDT: 1/2/2012 1:00:42 AM
TargetDate: 1/9/2012 8:00:00 AM
ResovledDT: 1/9/2012 1:19:23 PM

3) JP
CreatedDT: 1/2/2012 1:00:44 AM
TargetDate:1/9/2012 8:00:00 AM
ResovledDT:1/6/2012 2:46:32 PM

4) DG
CreatedDT: 6/29/2012 2:37:02 PM
TargetDate:7/31/2012 12:00:00 AM
ResovledDT: 7/13/2012 11:48:39 AM


5) PE
CreatedDT: 6/29/2012 9:24:27 AM
TargetDate:7/6/2012 9:22:03 AM
ResovledDT: 7/3/2012 4:34:33 PM

I tried testing your fomurla but it doesn't correct with the name.

One more thing, can you help me if the holiday is July 1 is SUnday, then Monday, people don't go to work, then don't count the work day on Monday, July 2.

Thank you.

quote:
Originally posted by TG

[code ] my formatted code [/code ]

just remove the trailing space before the close bracket ]

Be One with the Optimizer
TG


Edited by - JadeV on 07/16/2012 14:06:47
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 07/16/2012 :  14:15:01  Show Profile  Reply with Quote
Can't you put 20120702 on your holydays table?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  14:23:51  Show Profile  Reply with Quote
I'll try to ask you one more time:
PLEASE post the expected results for both [Busi_Hrs_Targ_Resl] and [Busi_Hrs_Cr_Resl] base on your 5 examples (above).

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  15:40:18  Show Profile  Reply with Quote
I updated my post (07/13/2012 : 17:17:13)
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 hours
if 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,21
BusiHr_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.

Be One with the Optimizer
TG

Edited by - TG on 07/16/2012 16:05:20
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  17:13:59  Show Profile  Reply with Quote
Thank you. That was helpful. However...

quote:
TOTAL: 4.45 + 32 + 2.36 = 38.81

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

Time from which column?

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/16/2012 :  18:26:58  Show Profile  Reply with Quote
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.

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/17/2012 :  15:58:05  Show Profile  Reply with Quote
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.


BK 
CreatedDateTime: '2012-01-03 11:15:00.000' 
ResolvedDateTime:  '2012-01-10 10:36:00.000'
1/3		      4:45
1/4		      8:00
1/5		      8:00
1/6                   8:00
1/7                   0:00 sat
1/8		      0:00 sun
1/9		      8:00
1/10		      2:36
-------------------------
Busi_Hrs_Cr_Resl 39:21


BK 
TargetDate: '2012-01-03 00:00:00.000'
ResolvedDateTime:  '2012-01-10 10:36:00.000'
1/3		      4:00
1/4		      8:00
1/5		      8:00
1/6                   8:00
1/7                   0:00 sat
1/8		      0:00 sun
1/9		      8:00
1/10		      2:36
-------------------------
Busi_Hrs_Targ_Resl 38:36


Actual OUTPUT:
ParentID    AssignedTo CreatedDateTime         TargetDate              ResolvedDateTime        TaskDurationInHrs Busi_Hrs_Cr_Resl Busi_Hrs_Targ_Resl Total_Work_Order
----------- ---------- ----------------------- ----------------------- ----------------------- ----------------- ---------------- ------------------ ----------------
0           BK         2012-01-03 11:15:00.000 2012-01-03 12:00:00.000 2012-01-10 10:36:00.000 0                 39:21            38:36              1
0           DG         2012-06-29 14:37:00.000 2012-07-31 00:00:00.000 2012-07-03 11:48:00.000 0                 5:11             00:00              1
0           JP         2012-01-02 01:00:00.000 2012-01-09 08:00:00.000 2012-01-06 14:46:00.000 0                 38:46            00:00              1
0           PE         2012-06-29 09:24:00.000 2012-07-06 09:22:00.000 2012-07-13 16:34:00.000 0                 78:36            46:38              1
0           TC         2012-01-02 01:00:00.000 2012-01-09 08:00:00.000 2012-01-09 13:19:00.000 0                 45:19            5:19               1

CODE:

if object_id('tempdb.dbo.#holidays')> 0 drop table #holidays
select CAST ('20120101' AS DATETIME) as [DatesOfHolidays],'New Years' AS HOLIDAYS
into #holidays
union all select '20120220', 'Family Day'
union all select '20120406', 'Good Friday'
union all select '20120409', 'Easter Monday'
union all select '20120525', 'Victoria Day'
union all select '20120701', 'Canada Day'
union all select '20120806', 'Heritage Day'
union all select '20120903', 'Labour Day'
union all select '20121008', 'ThanksGiving Day'
union all select '20121111', 'Remember Day'
union all select '20121225', 'Christmas Day'
union all select '20121226', 'Boxing Day'
union all select '20120702', ''

if object_id('tempdb.dbo.#task')> 0 drop table #task
create table #task
	(TaskDurationInHrs int
	, ParentID int
	, AssignedToTeam varchar(25)
	, AssignedTo varchar(10)
	, CreatedDateTime datetime
	, TargetDate datetime
	, ResolvedDateTime datetime
	, Bu_Hrs_Cr_Resl money
	, Bu_Hrs_Tg_Resl money)
insert #task
select           0, 0, 'Telus-Telecom', 'BK', convert(datetime,'1/3/2012 11:15:00 AM'), convert(datetime, '1/3/2012 12:00:00'), convert(datetime, '1/10/2012 10:36:00 AM'), 0.00, 25.03
union all select 0, 0, 'Telus-Telecom', 'TC', convert(datetime,'1/2/2012 1:00:00 AM'), convert(datetime, '1/9/2012 8:00:00 AM'), convert(datetime, '1/9/2012 1:19:00 PM'), 0.00, 25.03
union all select 0, 0, 'Telus-Telecom', 'JP', convert(datetime,'1/2/2012 1:00:00 AM'), convert(datetime, '1/9/2012 8:00:00 AM'), convert(datetime, '1/6/2012 2:46:00 PM'), 0.00, 25.03
union all select 0, 0, 'Telus-Telecom', 'DG', convert(datetime,'6/29/2012 2:37:00 PM'), convert(datetime, '7/31/2012 12:00:00 AM'), convert(datetime, '7/3/2012 11:48:00 AM'), 0.00, 25.03
union all select 0, 0, 'Telus-Telecom', 'PE', convert(datetime,'6/29/2012 9:24:00 AM'), convert(datetime, '7/6/2012 9:22:00 AM'), convert(datetime, '7/13/2012 4:34:00 PM'), 0.00, 25.03

;with
	a as (select convert(bigint,0) as n union all select 0),
	b as (select 0 as n from a as a cross join a as b),
	c as (select 0 as n from b as a cross join b as b),
	d as (select 0 as n from c as a cross join c as b),
	nums as (select row_number() over (order by (select 1)) as num from d as a cross join d as b),
	hrs as (select dateadd(hour, -num, dateadd(day, datediff(day, -1, getdate()),0)) as hr from nums),
	calendar as (
		select hr
			,isBus = case
				when datepart(weekday,hr) between 2 and 6 --mon-fri assuming @@datefirst = 7
				--and datepart(hour, hr) between 8 and 16 --8:00-9:00AM through 4:00-5:00PM
				and datepart(hour, hr) between 8 and 15 --8:00-9:00AM through 3:00-4:00PM
				then 1
				else 0
				end
		from hrs)
select cr.ParentID
	,cr.AssignedTo
	,cr.CreatedDateTime
	,cr.TargetDate
	,cr.ResolvedDateTime
	,cr.TaskDurationInHrs
	,cr.Busi_Hrs_Cr_Resl
	,coalesce(ta.Busi_Hrs_Targ_Resl, '00:00') as Busi_Hrs_Targ_Resl
	,cr.Total_Work_Order
from	(
	select t1.ParentID
		,t1.AssignedTo
		,t1.CreatedDateTime
		,t1.TargetDate
		,t1.ResolvedDateTime
		,t1.TaskDurationInHrs
		
		--For the Busi_Hrs_Cr_Resl , I don't care the ResolvedDate > TargetDate or not, but care the duration between 2 dates must not include holiday and weekend and if the holiday in Saturday or Sunday, then the next Monday don't count for business hour ).
		--Busi_Hrs_Cr_Resl = resolvedDate - CreatedDate
		,Busi_Hrs_Cr_Resl = 
			convert(varchar(10), convert(int, sum(
			case
			when c.hr < t1.createdDateTime then datediff(minute, t1.createdDateTime, dateadd(hour, 1, c.hr)) 
			when datediff(minute, c.hr, t1.ResolvedDateTime) < 60 then datediff(minute, c.hr, t1.ResolvedDateTime) 
			else 60
			end
			) / 60))
			+ ':' + right('0' + 
			convert(varchar(10), convert(int, sum(
			case
			when c.hr < t1.createdDateTime then datediff(minute, t1.createdDateTime, dateadd(hour, 1, c.hr)) 
			when datediff(minute, c.hr, t1.ResolvedDateTime) < 60 then datediff(minute, c.hr, t1.ResolvedDateTime) 
			else 60
			end
			) % 60)),2)

			
		--Total Work Order 
		--calendar will mess up count(*) so use a logical key for task.
		,Total_Work_Order = count(distinct convert(varchar(500), t1.AssignedTo)
			+ convert(char(23),t1.CreatedDateTime,121) 
			+ convert(char(23),t1.TargetDate,121) 
			+ convert(char(23),t1.ResolvedDateTime,121))
		
	from	#task t1
	inner join calendar c
		on  (c.hr > t1.createdDateTime or datediff(minute, c.hr, t1.createdDateTime) < 60)
		and t1.ResolvedDateTime > c.hr
		and c.isBus = 1
	left outer join #holidays hol
		on datediff(day, hol.[DatesOfHolidays], c.hr) = 0
		and c.isBus = 1
	where	hol.[DatesOfHolidays] is null
	and	t1.createddatetime between '2012-01-01 00:00:00.000' and getdate()
	and	(t1.AssignedToTeam = 'Telus-Telecom' or t1.AssignedToTeam = 'Telus-AOS')
	and	t1.assignedTo <> ''
	and	t1.AssignedTo is not null

	group by t1.ParentID
		,t1.AssignedTo
		,t1.CreatedDateTime
		,t1.TargetDate
		,t1.ResolvedDateTime
		,t1.TaskDurationInHrs
	) cr
left outer join 
	(
	select t1.ParentID
		,t1.AssignedTo
		,t1.CreatedDateTime
		,t1.TargetDate
		,t1.ResolvedDateTime
		
		--if ResolvedDate < TargetDate and between business hours, not the day holiday and weekend, 
		--and (if the holiday in Saturday or Sunday, then the next Monday don't count for business hour )
		--then 0, else ResolvedDate - TargetDate

		,Busi_Hrs_Targ_Resl = 
			convert(varchar(10), convert(int, sum(
			case
			when c.hr < t1.targetDate then datediff(minute, t1.targetDate, dateadd(hour, 1, c.hr)) 
			when datediff(minute, c.hr, t1.ResolvedDateTime) < 60 then datediff(minute, c.hr, t1.ResolvedDateTime) 
			else 60
			end
			) / 60))
			+ ':' + right('0' + 
			convert(varchar(10), convert(int, sum(
			case
			when c.hr < t1.targetDate then datediff(minute, t1.targetDate, dateadd(hour, 1, c.hr)) 
			when datediff(minute, c.hr, t1.ResolvedDateTime) < 60 then datediff(minute, c.hr, t1.ResolvedDateTime) 
			else 60
			end
			) % 60)),2)

	from	#task t1
	inner join calendar c
		on  (c.hr > t1.targetDate or datediff(minute, c.hr, t1.targetDate) < 60)
		and t1.ResolvedDateTime > c.hr
		and c.isBus = 1
	left outer join #holidays hol
		on datediff(day, hol.[DatesOfHolidays], c.hr) = 0
		and c.isBus = 1
	where	hol.[DatesOfHolidays] is null
	and	t1.targetDate between '2012-01-01 00:00:00.000' and getdate()
	and	(t1.AssignedToTeam = 'Telus-Telecom' or t1.AssignedToTeam = 'Telus-AOS')
	and	t1.assignedTo <> ''
	and	t1.AssignedTo is not null
	and	t1.targetDate < t1.resolvedDateTime
	group by t1.ParentID
		,t1.AssignedTo
		,t1.CreatedDateTime
		,t1.TargetDate
		,t1.ResolvedDateTime
		,t1.TaskDurationInHrs
	having right(max(convert(char(23), t1.resolvedDateTime, 121) + convert(char(1), c.isBus)), 1) = 1
	) ta
	on ta.parentid = cr.parentid
	and ta.assignedTo = cr.assignedTo
	and ta.createdDatetime = cr.createdDatetime
	and ta.targetDate = cr.targetDate
	and ta.resolvedDateTime = cr.resolvedDateTime


Be One with the Optimizer
TG

Edited by - TG on 07/18/2012 13:26:38
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/18/2012 :  13:17:33  Show Profile  Reply with Quote
Hey TG,
I joined your code with my database, and it works very well.
THank you very much for your help.
:)
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 07/18/2012 :  13:26:58  Show Profile  Reply with Quote
You're welcome. I'm glad it worked for you.

Be One with the Optimizer
TG
Go to Top of Page

sandeepmittal11
Starting Member

India
6 Posts

Posted - 10/08/2012 :  00:43:26  Show Profile  Reply with Quote
Refer this link
http://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.html

Regards,
Sandeep
Go to Top of Page

sandeepmittal11
Starting Member

India
6 Posts

Posted - 10/08/2012 :  00:45:39  Show Profile  Reply with Quote
<b>Refer this link</b>
http://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.html

Regards,
Sandeep
Go to Top of Page

sandeepmittal11
Starting Member

India
6 Posts

Posted - 10/08/2012 :  00:46:47  Show Profile  Reply with Quote
Refer this link
http://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.html

Regards,
Sandeep
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5942 Posts

Posted - 10/08/2012 :  13:49:55  Show Profile  Reply with Quote
quote:
Originally posted by sandeepmittal11

Refer this link
http://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.html

Regards,
Sandeep


Thanks Sandeep.
But your solution (attempts to) solve a different problem - it just totals actual hours recorded for each calendar day. And if your time goes through midnight it seems to break.

The O.P. only wants to consider only the "clocked" hours that fall within a pre-defined work-day. It excludes hours that are "non-work hours" like after 5:00 PM and holidays.

Be One with the Optimizer
TG
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000