Multiply columns in T-SQL join query

Question

I am using SQL Server 2008. I am trying to build a T-SQL query to calculate some performance metrics based on data from several tables. Unfortunately, I am stuck on one of the calculations and can not figure out what is wrong. I would greatly appreciate any help:

1. The calculations require the total produced (from table ShiftHourCounts), the total scrap pieces (from table ShiftReportScrap), and the total downtime (from Downtime query)

2. I have tried to add each operation/calculation in the query mainly for my own education/ troubleshooting

3. I do not understand why column Q returns zero. Q= Tok/Tp, and the query correctly returns and calculates both Tok and Tp individually. In the below example, Q should =0.994

4. The query currently returns correct values for everything except Q, A, P, and OEE. Q, A, P, and OEE always return zero

Current query:

--OEE= A*P*Q (this is the final desired result/ calculation)
--A= (Planned run time - Unplanned Down Time)/Planned run time
--A= (Prt - Dtu)/Prt
--Prt= Maximum Available Time - Planned Down Time
--Prt= Mat=DTp
--Effective production time= Planned run time - Unplanned Down Time
--Ept=Prt-DTu
--P= (BDT*total number of produced parts)/Effective production time
--P= (BDT*Tp)/Ept
--Q= Total number of OK parts/Total number of produced parts
--Q= Tok/Tp

select 
    sm.SR_ID, sm.SR_PartID, sm.SR_StartTime, 
    isnull(sm.SR_EndTime,GETDATE()) AS EndTime, 
    isnull(sm.SR_BDT,1) AS BDT, 
    DATEDIFF(n, sm.SR_StartTime, isnull(sm.SR_EndTime, GETDATE())) AS Prt, 
    isnull(p.TotalProduced,0) AS Tp, 
    isnull(s.Scrap,0) AS Scrap,
    (isnull(p.TotalProduced, 0) - isnull(s.Scrap, 0)) AS Tok, 
    isnull(dt.DownTimeDuration, 0) AS DTu,
    ((isnull(p.TotalProduced, 0) - isnull(s.Scrap, 0)) / isnull(p.TotalProduced, 0)) AS Q, --Q= Tok/Tp
    ((DATEDIFF(n, sm.SR_StartTime, isnull(sm.SR_EndTime, GETDATE())) - isnull(dt.DownTimeDuration, 0)) / DATEDIFF(n, sm.SR_StartTime, isnull(sm.SR_EndTime, GETDATE()))) AS A, 
    ((isnull(sm.SR_BDT, 1) * isnull(p.TotalProduced, 0)) / (DATEDIFF(n, sm.SR_StartTime, isnull(sm.SR_EndTime, GETDATE())) - isnull(dt.DownTimeDuration, 0))) AS P,
    (((isnull(p.TotalProduced, 0) - isnull(s.Scrap, 0)) / isnull(p.TotalProduced, 0)) * ((DATEDIFF(n, sm.SR_StartTime, isnull(sm.SR_EndTime, GETDATE())) - isnull(dt.DownTimeDuration, 0)) / DATEDIFF(n, sm.SR_StartTime, isnull(sm.SR_EndTime, GETDATE())))*((isnull(sm.SR_BDT,1)*isnull(p.TotalProduced,0))/(DATEDIFF(n,sm.SR_StartTime,isnull(sm.SR_EndTime,GETDATE()))-isnull(dt.DownTimeDuration,0)))) AS OEE
FROM 
    ShiftReportMaster sm 
LEFT JOIN 
    (SELECT 
         SH_ShiftID, Sum(SH_Produced) AS TotalProduced
     FROM 
         ShiftHourCounts
     GROUP BY 
         SH_ShiftID) p ON (p.SH_ShiftID = sm.SR_ID)
LEFT JOIN 
    (SELECT 
         SRS_SR_ID, SRS_PartID, Sum(SRS_Scraped) AS Scrap
     FROM 
         ShiftReportScrap
     GROUP BY 
         SRS_SR_ID, SRS_PartID) s ON (s.SRS_SR_ID = sm.SR_ID) 
                                  AND (s.SRS_PartID = sm.SR_PartID)
LEFT JOIN 
    (SELECT 
         srd.DTR_SRID, [Downtime reasons].DT_Planned, 
         Sum(srd.DTR_DownTimeDuration) AS DownTimeDuration
     FROM 
         ShiftReportDowntime srd 
     LEFT JOIN 
         [Downtime reasons] ON srd.DTR_Reason = [Downtime reasons].DT_ID
     GROUP BY 
         srd.DTR_SRID, [Downtime reasons].DT_Planned
    HAVING 
        ((([Downtime reasons].DT_Planned) = 0))) dt ON (dt.DTR_SRID = sm.SR_ID)
WHERE
    sm.SR_ID = 3689;

Answer 1

Most likely from integer division. Try this:

((isnull(p.TotalProduced+.0,0.0)-isnull(s.Scrap+.0,0.0))
/nullif(p.TotalProduced,0)) AS Q, --Q= Tok/Tp

Adding .0 or multiplying 1.0 implictly converts the integers into a decimal type.

Dividing integers will return an integer type, and if that value is less than 1 it will return 0 because it truncates instead of rounding or using some other logic to return an integer.