Calculate the weight of a working day within month

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

Transact-SQL (2005)

Calculate the weight of a working day within month

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-29 : 08:19:37

Hi, I need help please.

I'm struggling to return the weight of a working day within a month.

I am returning 4 columns, with last one being the weight calculation.

select tmpdates
,NumDaysInMonth
,Work_Days
,sum((Work_Days/NumDaysInMonth)*1) as Day_Weight

Cols & results:
tmpdates = 2009-01-01 00:00:00
NumDaysInMonth = 31
Work_Days = 21
Day_Weight = 1

I tried a few calculations, but i alwayy end up with 1 or 0.

Secondly the Day_Weight should be in #.### decimal result eg: 0.038

Regards

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-29 : 08:27:19

[code]select tmpdates
,NumDaysInMonth
,Work_Days
,sum((Work_Days/NumDaysInMonth)*1.0) as Day_Weight[/code]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-29 : 08:29:55

Forgot to mention the above result was for one row only.

The complete result is all the days for the month.
eg: 31 days, NumDaysInMonth = 31, Work_Days = 21 for that specific month

with Day_weight value = ?
and sum of Day_weight * 21 working days will always = 1

Regards

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-29 : 08:33:55

Thanks for the help.

I tried the code but my result for the 31 days all = 0.0

it should return the weight (?) calculated by the amount of work_d days will add up to 1

like explained in previous post.

Regards

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-29 : 09:08:24

Try this,

select tmpdates
,NumDaysInMonth
,Work_Days
,sum((convert(numeric(15,5),Work_Days)/NumDaysInMonth)*1.0) as Day_Weight

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:07:20

shouldnt you be grouping before taking sum? something like below


select tmpdates
,NumDaysInMonth
,Work_Days
,sum((Work_Days*1.000/NumDaysInMonth)) as Day_Weight
group by tmpdates
,NumDaysInMonth
,Work_Days

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 02:37:57

Hi, Thank You all for the assist.

I have tried both methods, I have a grouping below before the sum.

But the result value for does not add up:
NumDaysInMonth = 31
Work_Days = 21

Day_Weight result for both = 0.67741935483870960

& when one adds them up 0.67741935483870960 x 21 Wordays does not = 1

I had a look at the sheet & the value returned is:
NumDaysInMonth = 31
Work_Days = 21
Day_Weight = 0.0476

secondly how to round to return only: #.####

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-30 : 03:02:28

SELECT NumDaysInMonth, Work_Days, 1.0E / Work_Days AS Day_Weight

E 12°55'05.63"
N 56°04'39.26"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 03:37:14

Thank You - Great stuff!

I also just figured it out the calculater: 1/21 = 0.0476190476190476

How do I round the value to: 0.0476

Once again Thank You

Thiyagu_04
Starting Member

37 Posts

Posted - 2008-12-30 : 03:47:30

select ROUND(0.0476190476190476,5)

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-30 : 03:48:59

Thank You!

round(Day_Weight,4)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources