SQL integer division returns 0 rows

user_16299404 0
Viewed 35

I am trying to create a calculated field called efficiency %. This is calculated by taking LaborDtl.LaborHrs minus Calculated_Shouldhavetaken divided by Calculated_Shouldhavetaken. When I run the query I get zero rows returned, but creating the calculated field without the division returns the difference as expected. I believe this is due to how division is treated in the server.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
    [LaborDtl].[JobNum] as [LaborDtl_JobNum],
    [JobHead].[PartNum] as [JobHead_PartNum],
    [LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
    [LaborDtl].[OpCode] as [LaborDtl_OpCode],
    [JobHead].[ProdQty] as [JobHead_ProdQty],
    (LaborDtl.LaborQty + LaborDtl.ScrapQty) as [Calculated_TotalQty],
    [LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
    (TotalQty/ JobHead.ProdQty* LaborDtl.LaborHrs* 1.0) as [Calculated_Shouldhavetaken],
    [LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
    ((LaborDtl.LaborHrs- Shouldhavetaken)/ Shouldhavetaken) as [Calculated_effpercent]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobOper as JobOper on 
    LaborDtl.Company = JobOper.Company
    and LaborDtl.JobNum = JobOper.JobNum
    and LaborDtl.AssemblySeq = JobOper.AssemblySeq
    and LaborDtl.OprSeq = JobOper.OprSeq
inner join Erp.JobOpDtl as JobOpDtl on 
    JobOper.Company = JobOpDtl.Company
    and JobOper.JobNum = JobOpDtl.JobNum
    and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
    and JobOper.OprSeq = JobOpDtl.OprSeq
inner join Erp.JobHead as JobHead on 
    JobOpDtl.Company = JobHead.Company
    and JobOpDtl.JobNum = JobHead.JobNum
where (LaborDtl.ClockInDate = dateadd (day, -1, Constants.Today))

Any guidance would be appreciated.

Thanks

sql
1 Answers

It could be as you mentioned. Any chance you could encapsulate the query and do your calculation for the division part outside just to test it?

Try casting decimal around Shouldhavetaken

CAST(Shouldhavetaken AS DECIMAL(9,2))

or something like that. See if works.

Another test you can run is to hardcode an int as the divisor and hardcode a decimal and see if you get different results. / 2 or / 2.0

user_34082450
Related