SQL Server Forums Profile | Register | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  Calculate total working hours New Topic  Reply to Topic  Printer Friendly
Author  Topic
Page: of 2

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/13/2012 :  17:17:13 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 16and DateDiff(hh,0,t1.ResolvedDateTime)%24 between 8 and 16and 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 postsBe One with the OptimizerTG Edited by - TG on 07/16/2012 18:18:10

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/13/2012 :  18:19:30 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 OptimizerTG

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  11:26:52 quote:Originally posted by JadeVGood morning TG,My example data is:AssignedTo CreatedDT TargetDate ResolvedDT Bu_Hrs_Cr_Resl Bu_Hrs_Tg_ReslGH 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.35MM 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 hoursBe One with the OptimizerTG

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  11:42:48 quote:Originally posted by JadeVI 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 OptimizerTG Edited by - TG on 07/16/2012 11:48:06

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  12:19:41 >>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 hoursbut: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 OptimizerTG

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  12:46:25 [code ] my formatted code [/code ]just remove the trailing space before the close bracket ]Be One with the OptimizerTG

Yak Posting Veteran

62 Posts

 Posted - 07/16/2012 :  14:05:47 HI TG,I checked and I want to do like this1) business_hours = resolvedDT - createdDT when createdDT = ResolvedDT2) 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 AMTargetDate: 12/6/2011 12:28:09 PMResovledDT: 1/23/2012 11:39:35 AM2) TCCreatedDT: 1/2/2012 1:00:42 AMTargetDate: 1/9/2012 8:00:00 AMResovledDT: 1/9/2012 1:19:23 PM3) JP CreatedDT: 1/2/2012 1:00:44 AMTargetDate:1/9/2012 8:00:00 AMResovledDT:1/6/2012 2:46:32 PM4) DGCreatedDT: 6/29/2012 2:37:02 PM TargetDate:7/31/2012 12:00:00 AMResovledDT: 7/13/2012 11:48:39 AM5) PECreatedDT: 6/29/2012 9:24:27 AMTargetDate:7/6/2012 9:22:03 AMResovledDT: 7/3/2012 4:34:33 PMI 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 OptimizerTG Edited by - JadeV on 07/16/2012 14:06:47

jleitao
Yak Posting Veteran

Portugal
52 Posts

 Posted - 07/16/2012 :  14:15:01 Can't you put 20120702 on your holydays table?

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  14:23:51 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 OptimizerTG

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  17:13:59 Thank you. That was helpful. However...quote:TOTAL: 4.45 + 32 + 2.36 = 38.81is 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 OptimizerTG

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/16/2012 :  18:26:58 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 OptimizerTG

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/17/2012 :  15:58:05 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 OptimizerTG Edited by - TG on 07/18/2012 13:26:38

Yak Posting Veteran

62 Posts

 Posted - 07/18/2012 :  13:17:33 Hey TG,I joined your code with my database, and it works very well.THank you very much for your help.:)

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 07/18/2012 :  13:26:58 You're welcome. I'm glad it worked for you.Be One with the OptimizerTG

sandeepmittal11
Starting Member

India
6 Posts

 Posted - 10/08/2012 :  00:43:26 Refer this linkhttp://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.htmlRegards,Sandeep

sandeepmittal11
Starting Member

India
6 Posts

 Posted - 10/08/2012 :  00:45:39 Refer this linkhttp://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.htmlRegards,Sandeep

sandeepmittal11
Starting Member

India
6 Posts

 Posted - 10/08/2012 :  00:46:47 Refer this linkhttp://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.htmlRegards,Sandeep

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts

 Posted - 10/08/2012 :  13:49:55 quote:Originally posted by sandeepmittal11Refer this linkhttp://itdeveloperzone.blogspot.in/2012/03/calculate-working-hours-sql-server.htmlRegards,SandeepThanks 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 OptimizerTG
Page: of 2  Topic
 New Topic  Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC