-- SOS Taget Achievement

SELECT store_id,SOSHeaderName,ChildSelfFacing,SOSHeaderFacing,IFNULL(Avg_SOSTarget, 0) AS Avg_SOSTarget,
ROUND(ChildSelfFacing * 1.0 / NULLIF(SOSHeaderFacing, 0),6) AS SOS,
IF((ChildSelfFacing * 1.0 /NULLIF(SOSHeaderFacing, 0)) >= (IFNULL(Avg_SOSTarget,0) / 100.0),
1,0) AS SOSCompliance,
LEAST(100.0,
ROUND(((ChildSelfFacing * 1.0 /NULLIF(SOSHeaderFacing, 0))/NULLIF(IFNULL(Avg_SOSTarget,0) / 100.0, 0)) * 100,2)) AS SOSAchievement_Percentage
FROM
(
SELECT store_id,SOSHeaderName,
SUM(ChildSelfFacing) AS ChildSelfFacing,SUM(SOSHeaderFacing) AS SOSHeaderFacing,
IFNULL(ROUND(AVG(SOSTarget), 2),0) AS Avg_SOSTarget FROM DaburIndia_BI.SOS_OneApp
GROUP BY store_id, SOSHeaderName) t
ORDER BY store_id,SOSHeaderName;