How to get the number of hours Working

Question

How to get the number of hours Working

Viewing 5 posts - 1 through 6 (of 6 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: 1004664 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)