Find Working Hours 9-5pm SQL 1

[SQLRory]

Hi guys,

I am currently writing a report in which I will need to find the working hours between two date time values.

One value will be 20080101 09:00 and another 20080201 17:00

Idealy a function something like
WORKINGHOURS('20080101 09:00','20080201').
WORKINGHOURS('FIELD1','FIELD2')

I know I am asking a lot but this would be amazing if someone had an idea how to do this?

I have tried a million and one CASE WHEN's when using the DATEPART specifing the day etc I still can't crack it.

Does anyone have a simple solution?

Thanks

Rory

 

[gmmastros]

This is not exactly what you were looking for, but it's close.

thread183-1246063

Take a look at that one. If you have any further questions, don't hesitate to ask.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLRory]

Thanks gmmastros,

It unfolds that on the specific database I am writing against I am not allowed permissions to create any functions, stored procedures etc.

If you know of a way execute something like this within the sript I would much appreciate this.

Thanks

Rory

 

[gmmastros]

What are you allowed to do?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jamfool]

umm...bit rough

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20080101'
SET @dEnd = '20080201'


select (count(*)*8)+8 as WorkHours from
(
select top 100 percent datename(dw,@dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) as r,
@dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd
order by range_date
) d
where d.r not in ('Saturday','Sunday')

--This assume that your first date starts at nine and end date is 5pm, anything more complicated than this will need you to adjust
--(8xworkdays+8hours)

--Also not that this solution will not work for periods of time greater than 10 years

 

[SQLRory]

Thanks Jamfool and George.

Jamfool that post was awesome, it makes not sense at all but works great thanks.

Rory

 

[gmmastros]

>> SET @dBegin = '20080101' -- Jan 1, 2008
>> SET @dEnd = '20080201' -- Feb 1, 2008

So... the number of working hours for the month of January 2008, right?

Jan 1, 2008 is Tuesday. So week 1 has 4 working days.
Jan 31, 2008 is Thursday. So last week has 4 working days.
There are 3 full weeks in January 2008.

4 for week 1, Jan 1 -> Jan 4
5 for week 2, Jan 7 -> Jan 11
5 for week 3, Jan 14 -> Jan 18
5 for week 4, Jan 21 -> Jan 25
4 for week 5, Jan 28 -> Jan 31

total working days in January = 23

23 days * 8 hours per day = [!]184[/!] hours

Using the code posted by jamfool, the result is [!]200[/!] hours.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLRory]

George,

There was a small error.

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20080101'
SET @dEnd = '20080201'

-- The below shouldn't have the (+8) as this adds an extra working day for no reason.
--Also if looking for working hours in Jan parameters would look like
--@dBegin '20080101'
--@dEnd '20070131'
select (count(*)*8)+8 as WorkHours from
(
select top 100 percent datename(dw,@dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) as r,
@dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd
order by range_date
) d
where d.r not in ('Saturday','Sunday')

Thanks

Rory

 

[gmmastros]

SQLRory,

The intent of my most recent post was to point out that a bug existed, and that it's still there. According to the comments in your code, for the month of January, the input variables should be....

[tt][blue]
set @dBegin = '20080101'
set @dEnd = '20080131'
[/blue][/tt]

When I use those parameters with the code you posted, I get 192 hours. But (as I pointed out above), there should be 184 hours for the month of January.

The biggest point I am trying to make is that there are many people on this site that are willing to help you. However, it's important that you verify the queries. Test, test, and test again.

Please do not mis-interpret this post as 'picking on' Jamfool. I'm not. I have no doubt that some of the advice I give is sometimes wrong. We all try pretty hard to make sure that doesn't happen, but we all make mistakes, too. Ultimately it is your responsibility to verify the advice given.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLRory]

Hi George,

I fully understand. I am an novice and any advise is better than no advise. I am getting there and am becoming strong in SQL so thanks.

Thanks

Rory