dividing integer numbers

I have three separate SQL queries and I'm trying to divide but my results =0.  It should = 100.

SQL1                                                                                                          SQL2
select sum(cast(measurement as int)) AS KPIValue                    select sum(AssetStatusHistory.DownTime) AS KPIValue
From WOTask                                                                                      From AssetStatusHistory  
inner join wo on wo.wopk=WOTask.wopk                                      LEFT JOIN Asset on Asset.AssetPk =
WHERE                                                                                                   AssetStatusHistory.AssetPK
Measurement NOT LIKE '%[^0-9]%'                                                   RESULT = 0
RESULT = 2130

So I subtract SQL 1 from SQL2 then divide by SQL1 *100 which should give me 100%


Any suggestions!
DJ StoneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
can you show us how are you getting your final result?
0
DJ StoneAuthor Commented:
Hello arana,   my final result just returns 0.
0
aranaCommented:
Yes i understand that part, I want to see the code on how are you calculating, doing your substraction and division
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DJ StoneAuthor Commented:
I am creating this as a KPI within a third party software

select sum(cast(measurement as float)) AS KPIValueFrom WOTaskinner join wo on wo.wopk=WOTask.wopk WHERE Measurement NOT LIKE '[^0-9]' ANDWO.TargetDate>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)  1AND SpecificationPK= 140AND wo.RepairCenterID = 'HDWV' AND WO.RepairCenterPK IN (52) SUBTRACT select sum(AssetStatusHistory.DownTime) AS KPIValueFrom AssetStatusHistory  LEFT JOIN Asset on Asset.AssetPk = AssetStatusHistory.AssetPK WHERE assetstatushistory.shutdowndate >=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)  1 AND Asset.Priority = 1 AND asset.RepairCenterID = 'HDWV' AND Asset.RepairCenterPK IN (52) DIVIDEselect ISNULL(sum(cast(measurement as int)), '') AS KPIValueFrom WOTaskinner join wo on wo.wopk=WOTask.wopk WHERE Measurement NOT LIKE '[^0-9]' ANDWO.TargetDate>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)  1 AND SpecificationPK= 140 AND wo.RepairCenterID = 'HDWV' AND WO.RepairCenterPK IN (52)
0
aranaCommented:
I still dont see where you are multiplying by 100, but I think i know whats going on, ORDER OF PRECEDENCE of substract and divide,

try using parentheses in each part ( i still think the multiply part missing)
SELECT 
(
sum(cast(measurement AS FLOAT)) AS KPIValue
FROM WOTaskinner
JOIN wo ON wo.wopk = WOTask.wopk
WHERE Measurement NOT LIKE '[^0-9]'
	AND WO.TargetDate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) 1
	AND SpecificationPK = 140
	AND wo.RepairCenterID = 'HDWV'
	AND WO.RepairCenterPK IN (52) SUBTRACT

SELECT sum(AssetStatusHistory.DownTime) AS KPIValueFrom AssetStatusHistory
LEFT JOIN Asset ON Asset.AssetPk = AssetStatusHistory.AssetPK
WHERE assetstatushistory.shutdowndate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) 1
	AND Asset.Priority = 1
	AND asset.RepairCenterID = 'HDWV'
	AND Asset.RepairCenterPK IN (52) 
)
	
	DIVIDE

SELECT ISNULL(sum(cast(measurement AS INT)), '') AS KPIValueFrom WOTaskinner
JOIN wo ON wo.wopk = WOTask.wopk
WHERE Measurement NOT LIKE '[^0-9]' ANDWO.TargetDate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) 1
	AND SpecificationPK = 140
	AND wo.RepairCenterID = 'HDWV'
	AND WO.RepairCenterPK IN (52)

Open in new window

0
DJ StoneAuthor Commented:
Each sql query is placed in a separate Each query is placed in seperate field

I am unable to place the query in parentheses.
Query-image-2.PNG
0
DJ StoneAuthor Commented:
Is it because the measurement table is an integer?
0
aranaCommented:
It is most likely because everytime you do operations without parentheses they are made in the following order

Multiplication, division, and remainders
then
Addition and subtraction

Oh another thing I noticed, your sql2 doesn't return 0 it returns nothing or null which is not the same, and may also be the cause of your problems.
so try

isnull(sum(AssetStatusHistory.DownTime),0)

or maybe (is this db2 you are using?)
IFNULL(sum(AssetStatusHistory.DownTime),0)

or just make sure somehow your query returns at least a 0 and not an empty value or null.

I dont know how KPI puts the queries together, but seems from your previous post that the multiplication is getting done before the substraction leading you to a 0 value (no matter how much operations you do if you multiply by 0 you get 0)
0
SharathData EngineerCommented:
Can you provide the result of SQL1 and SQL2?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
( Since I don't see this concept anywhere in this thread )

SQL Server has a lovely feature called 'integer math', where if you divide two integers SQL ass-u-me's that you want the product to be an integer, even though most people want the decimal portion.  For example, run this in your SSMS...
SELECT 4 / 5
SELECT 4 / 5.00
SELECT CAST(4 / CAST(5 AS DECIMAL(2,1)) AS DECIMAL(2,1))

Open in new window

The first SELECT returns a 0, because integer divided by integer returns an integer.
The second SELECT, which is what you and most normal people are expecting, returns .80000.  The way to return the decimal portion is to change the data type of either of the values to something that can handle decimals.
The third SELECT just returns 0.8.  Not sure why the second one returns the extra .0's, perhaps another expert can chime in with that answer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Pretty confident my comment is the T-SQL answer to this question, but since there are other factors in play I'm open to interpretation as far as the correct split here.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Over a month and no follow-up comments..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.