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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/09/2012 :  16:39:44  Show Profile  Reply with Quote
I am working on a report name " calculate the total working hours: which only count the time between in business hours from 8:am to 17:00pm. Except Saturday and Sunday and holiday.

I don't know how to get the time which is not include after business hours from 17:01pm to 6:59 am and parameters to except the holidays

Anyone can help me please!

Thank you

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/09/2012 :  17:36:40  Show Profile  Reply with Quote
see

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  11:13:03  Show Profile  Reply with Quote
Thanks for your link.
I don't have a permission to write a procedure, I only write query on sql Server 2005 and then run it and use it for Crystal Report. So what should I do ?

Also I tried to get the exactly hours between 2 days, but the number is not exactly, it is round number.
For example:
CreatedDateTime is 2012-07-02 08:30:00.000
ResolvedDateTime is 2012-0704 09:45:00.000
It must be : Total working hours is 17.15 hours
But I got 17 hours

My formula : DateDiff(hh,CreatedDateTime, ResolvedDateTime)

PLease let's me know how can get the float number?

Thank you very much
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/12/2012 :  11:36:12  Show Profile  Reply with Quote
for partial hours problem use:
DateDiff(minute,CreatedDateTime, ResolvedDateTime) / 60.0

Can you take the code from the blog and use it as sql without the CREATE PROCEDURE part?
or -
calculating business hours is not just a simple few lines of code. Maybe you can request that a procedure or function be created on the server for you.

Be One with the Optimizer
TG
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  12:24:30  Show Profile  Reply with Quote
HI TG.

I did:
for partial hours problem use:
DateDiff(minute,CreatedDateTime, ResolvedDateTime) / 60.0

Yeah, I got it. Thanks TG. Because I did
DateDiff(minute,CreatedDateTime, ResolvedDateTime) / 60
therefore, it didn't get float number.
I will try another part without CREATE PROCEDURE
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  12:29:22  Show Profile  Reply with Quote
TG, how can I get the format number only 2 decimal numbers.
Ex: 1.15
not 1.1500000
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 07/12/2012 :  13:12:48  Show Profile  Reply with Quote
CONVERT(Numeric(5,2), DateDiff(minute,CreatedDateTime, ResolvedDateTime) / 60.0)
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  13:47:48  Show Profile  Reply with Quote
Yeah, I got it.
Thank you jleitao.

Do you know how to find a duration day between createdDateTime, ResolvedDateTime where this "duration" contains weekend and holiday. If the holidays are on Saturday or Sunday, then don't count the work day on Monday?

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  13:53:58  Show Profile  Reply with Quote
HI jleitao,
unforNatuly, when I add "CONVERT" function into the code, it has this error

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting numeric to data type numeric.

Then I just only run the code with createdDateTime in 1 month.

How can I run the database in more than 1 month ?
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 07/12/2012 :  14:04:27  Show Profile  Reply with Quote
CONVERT(Numeric(18,2), DateDiff(minute,CreatedDateTime, ResolvedDateTime) / 60.0)

to see the weekday you can use DATEPART or DATENAME:
SELECT DATENAME(dw, getdate()) as name, DATEPART(dw, getdate()) as number

to holiday, NOT SURE, but probably you need a table with the holidays
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  16:03:30  Show Profile  Reply with Quote
Thanks jleitao.
I created a table like this,


select '20120101' AS DATE,'New Years' AS 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'

Then UNION ALL with the code, but it errors
"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

What should I do?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/12/2012 :  17:27:23  Show Profile  Reply with Quote

select CAST('20120101' AS datetime) AS [DATE],'New Years' AS 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'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/12/2012 :  17:35:30  Show Profile  Reply with Quote
Thanks Visakh16.
I am trying to make your code in the early morning into my code because I can't get the permission to write the procedure.
But still not get it yet.
However, I would like to ask you how can I get the duration day between createdDateTime and ResolvedDateTime where it has a holidays and Saturday and Sunday? Because I want to get the duration day, then at the WHERE CLAUSE, I would say
WHERE duration_Day between CreatedDateTIme and ResolvedDateTime not in ('holidays','weekend')
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/12/2012 :  18:05:56  Show Profile  Reply with Quote
So you can create a table but not a stored procedure?

Well, this might give you some ideas. Here is a version that doesn't require any functions or called procedures. If Crystal Reports doesn't support CTEs then this can be turned into a derived table.

The idea is the CTE creates a calendar table which has a row for every hour and an attribute indicating if it is a business hour or not. If this is a permanent table then you can put in holidays and early release days etc. So all m-f 8-5 hours are marked as 1. All other hours are marked as 0.

The logic then sums hours (or partial hours) for each business hour by person. Of course you could add in other groupings like period.


if object_id('tempdb.dbo.#hours') > 0 drop table #hours
if object_id('tempdb.dbo.#holidays') > 0 drop table #holidays

create table #hours(person varchar(10), startTime datetime, endTime datetime)
insert #hours 
select 'theo', '2012-07-10 8:15:00.00', '2012-07-10 16:45:00.00'
union all select 'theo', '2012-07-11 8:01:00.00', '2012-07-11 17:45:00.00'
union all select 'theo', '2012-07-09 8:15:00.00', '2012-07-09 16:46:00.00'
union all select 'Jade', '2012-07-02 7:00:00.00', '2012-07-11 18:00:00.00'


select CAST('20120101' AS datetime) AS [DATE],'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'


;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
				then 1 
				else 0 
				end
		from	hrs)

select person,
	convert(decimal(6,2), sum(
		case
		when c.hr < h.startTime then datediff(minute, c.hr, h.startTime) 
		when h.endTime between c.hr and dateadd(hour, 1, c.hr) then datediff(minute, c.hr, h.endTime) 
		else 60
		end
		) / 60.0) BusinessHours
from calendar c
join	#hours h 
	on (hr > starttime or datediff(minute, hr, starttime) < 60)
	and (h.endTime > hr or datediff(minute, endtime, hr) < 60)
	and isBus = 1
left outer join #holidays hol
	on datediff(day, hol.[date], c.hr) = 0
where	hol.[date] is null
group by person
order by person 


OUTPUT:
person     BusinessHours
---------- ---------------------------------------
Jade       72.00
theo       24.03


EDIT:
corrected a problem with the BusinessHours expression

Be One with the Optimizer
TG

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

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/13/2012 :  11:31:21  Show Profile  Reply with Quote
Thank you very much for your prompt respond.
I am working on this with my database. Will let you know if I get the result.
:)
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/13/2012 :  11:58:26  Show Profile  Reply with Quote
You're welcome. Good luck - let us know...

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/13/2012 :  13:40:43  Show Profile  Reply with Quote
Your statement works as is up to the UNION ALL.

I don't have your [task] table so I can't run the portion after the UNION ALL.
However, for UNION'd statements you must match the number of columns returned and their datatypes for all parts.
AND if you use an ORDER BY clause it must be after all the union'd statements. ie:

<statement1>
UNION ALL
<statement2>
UNION ALL
<statement3>
ORDER BY

and each statement must return the same number of columns, with the same datatypes, in the same sequence.

EDIT:
the column names will be whatever they are defined as in the first statement.

If you post more code please maintain the formatting by using CODE tags. (see forum faq)

Be One with the Optimizer
TG

Edited by - TG on 07/13/2012 13:53:19
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/13/2012 :  13:56:01  Show Profile  Reply with Quote
yes, all of my statements works after UNION ALL are work.
I just want to combine with the check Holiday and weekend.
I will try again.
Thanks TG.
Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 07/13/2012 :  15:05:43  Show Profile  Reply with Quote
I can't use UNION ALL because statement2 doesn't have the same number of columns with statement1. :(

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/13/2012 :  15:23:08  Show Profile  Reply with Quote
You can always add bogus columns to each statement to make them similar.


select convert(datetime, null) as crdate
	,id
from sysobjects
UNION ALL
select crdate
	,convert(int, null) as id
from sysobjects
order by id




Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/13/2012 :  15:30:33  Show Profile  Reply with Quote
quote:
Originally posted by TG

You can always add bogus columns to each statement to make them similar.


select convert(datetime, null) as crdate
	,id
from sysobjects
UNION ALL
select crdate
	,convert(int, null) as id
from sysobjects
order by id



EDIT:
That would be if you want statement1 in different rows then statement2. If you need to combine the values across the same row then you would need some common value in both statements. Like perhaps [assignedTO]. If that is in both statements then you could probably get rid of the UNION ALL and add [task] table to the one I provided JOINed by assignedTO.

Be One with the Optimizer
TG



Be One with the Optimizer
TG
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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