How to get the number of hours Working

Question

How to get the number of hours Working

ash0550

Mr or Mrs. 500

Points: 517
More actions
June 17, 2011 at 12:25 pm

#253155

Proj_IDPRoj_MngrStartDateEndadateHours_allocated
123Bolla2011-06-072011-08-302000
768harsha 2010-09-262010-10-21524
4169vamsi2009-02-182009-07-272850
663vijay2008-04-092008-07-02786
4635sam2006-02-272009-05-1818000
NULLNULLNULLNULLNULL
Select (DATEDIFF(day,b.startDate,b.Endadate) +1) - (DATEDIFF(wk,b.StartDate,b.Endadate)*2)
- (CASE WHen DATENAME(dw,b.startdate)='SUNDAY' THen 1 else 0 end)
- (CASE when DATENAME(dw,b.endadate)='SATURDAY' then 1 else 0 end)
from Bolla b where Proj_ID=123;
I used the abv query and got the number of days. NOw I want to find the number of hours in the total duration.
I tried by multiplying the answer from the query with 24 but it showed me errors. I was not able to correct it. If any one could help me it would be great.
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 1

What is the error you are receiving?

When I run the query against the data set provided, the result is 61, and I'm able to multiply it by 24 without any problem.

~Rusty

ash0550 Mr or Mrs. 500 Points: 517 More actions · Answer 2

Could you tell me where did you do that multiplication I mean the Query

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 3

Here you go...

Select ((DATEDIFF(day,b.startDate,b.Endadate) +1) - (DATEDIFF(wk,b.StartDate,b.Endadate)*2)

- (CASE WHen DATENAME(dw,b.startdate)='SUNDAY' THen 1 else 0 end)

- (CASE when DATENAME(dw,b.endadate)='SATURDAY' then 1 else 0 end)) * 24

from Balla b where Proj_ID=123;

ash0550 Mr or Mrs. 500 Points: 517 More actions · Answer 4

oops thats a bit stupid one. I didnt check the brackets properly.

Actually I am trying to design a query that would allocate the number of hours per month based on the available days in a month.

Say In june there are 15 days so in the result of 1484 some hrs be allocated. IN that way

Anyways thanks

Jeff Moden SSC Guru Points: 1004556 More actions · Answer 5

At this point, my recommendation would be to build a Calendar or Date table. If you have problems finding a good one on Google or Yahoo (or whatever your favorite search engines are), c'mon back and we'll help you get started. But give it a look-see on your own, first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

How to get the number of hours Working

Cookies on SQLServerCentral