Solved

SQL Division

Posted on 2010-09-22
7
463 Views
Last Modified: 2012-05-10
i'm using the following code to divide two number but i want my result to be instead of a whole number but a decimal.  167 / 25 = 6.68.  when i use the following it gives me only 6.

convert(decimal,case when DaysWorked = 0 then 0 else ((TotalHours/60/60) / DaysWorked  end,2) as AverageLaborHours

TotalHours and DaysWorked are both integers.  
TotalHours is in seconds.  that is why i'm dividing it by 60 twice to get the 167.

Please help
0
Comment
Question by:thecruz
7 Comments
 
LVL 7

Assisted Solution

by:mquiroz
mquiroz earned 167 total points
ID: 33736521
you need somenthing like this:

convert(decimal(4, 2),case when DaysWorked = 0 then 0 else ((TotalHours/60/60) / DaysWorked  end,2) as AverageLaborHours

0
 
LVL 3

Assisted Solution

by:_bmendoza
_bmendoza earned 167 total points
ID: 33736672
select intcol1 / (intcol2 * 1.0)
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 166 total points
ID: 33736933
To go along with the last Experts post which shows a valid work around is that MS SQL does INTEGER division when both operands (the numerator and denominator) are INTEGERS; therefore, you have to cast/convert explicitly or implicitly as is the case with multiplying by 1.0 to get one of the operands to be a different data type.

If this is your original query:
convert(decimal,case when DaysWorked = 0 then 0 else ((TotalHours/60/60) / DaysWorked  end,2) as AverageLaborHours

You can simply do something like this:
convert(decimal,case when DaysWorked = 0 then 0 else ((TotalHours/60.0/60.0) / DaysWorked  end,2) as AverageLaborHours

Additional notes:
- to increase efficiency do simple math calculations out ahead of time -- so x / 60.0 / 60.0 is probably best done as x / 3600.0
- convert() syntax is convert({datatype}, {value}) -- decimal is declared as decimal(m, n) where m is total number of digits and n is the number of decimal places; therefore, if I understand you, you are looking for 2 decimal places -- this is incorrect currently and should be convert(decimal(12, 2), {case statement}) where you would replace 12 with different value if needed.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Closing Comment

by:thecruz
ID: 33737308
this did it
0
 
LVL 3

Expert Comment

by:_bmendoza
ID: 33737316
mwvisa1:- so to clarify is
                 select intcol1 / (intcol2 * 1.0)
will not work in ms sql server?
Zones:
MS SQL Server, SQL Server 2005, SQL Server 2008
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33737494
@_bmendoza:
"To go along with the last Experts post which shows a valid work around"

This statement was referring to your post.  I was saying to the thecruz that you post did indeed show a valid work around for the issue which I then explained along with the other corrections.  I was supporting your post.
0
 
LVL 3

Expert Comment

by:_bmendoza
ID: 33737540
Thanks=)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now