DJ Stone
asked on
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.Dow nTime) 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!
SQL1 SQL2
select sum(cast(measurement as int)) AS KPIValue select sum(AssetStatusHistory.Dow
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!
can you show us how are you getting your final result?
ASKER
Hello arana, my final result just returns 0.
Yes i understand that part, I want to see the code on how are you calculating, doing your substraction and division
ASKER
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.Dow nTime) AS KPIValueFrom AssetStatusHistory LEFT JOIN Asset on Asset.AssetPk = AssetStatusHistory.AssetPK WHERE assetstatushistory.shutdow ndate >=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(measuremen t 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)
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(
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)
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)
ASKER
Each sql query is placed in a separate
I am unable to place the query in parentheses.
Query-image-2.PNG
I am unable to place the query in parentheses.
Query-image-2.PNG
ASKER
Is it because the measurement table is an integer?
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(AssetStatusHist ory.DownTi me),0)
or maybe (is this db2 you are using?)
IFNULL(sum(AssetStatusHist ory.DownTi me),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)
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(AssetStatusHist
or maybe (is this db2 you are using?)
IFNULL(sum(AssetStatusHist
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)
Can you provide the result of SQL1 and SQL2?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Over a month and no follow-up comments..