Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Calculate total working hours

Author  Topic 

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-09 : 16:39:44
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

52326 Posts

Posted - 2012-07-09 : 17:36:40
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

62 Posts

Posted - 2012-07-12 : 11:13:03
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-12 : 11:36:12
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

62 Posts

Posted - 2012-07-12 : 12:24:30
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

62 Posts

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

jleitao
Posting Yak Master

100 Posts

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

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-12 : 13:47:48
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

62 Posts

Posted - 2012-07-12 : 13:53:58
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
Posting Yak Master

100 Posts

Posted - 2012-07-12 : 14:04:27
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

62 Posts

Posted - 2012-07-12 : 16:03:30
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

52326 Posts

Posted - 2012-07-12 : 17:27:23
[code]
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'
[/code]

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

Go to Top of Page

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-12 : 17:35:30
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-12 : 18:05:56
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
Go to Top of Page

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-13 : 11:31:21
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 11:58:26
You're welcome. Good luck - let us know...

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 13:40:43
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
Go to Top of Page

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-13 : 13:56:01
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

62 Posts

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

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 15:23:08
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 15:30:33
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
    Next Page

- Advertisement -