I'm trying to get a vendor's Delivery Performance like below result
TotalDelivery 51 OnTime 50 Delayed 1 Percentage 98
With below SQL I'm getting PERCENTAGE value always 0 What am doing wrong ? Thank You
Orders Table :
ID, CUSTNO, TDATE, DELV_ID, DELV_DATE
Delivery Table :
ID, CUSTNO, TDATE, ORDER_ID
WITH CTE AS
(
SELECT
D.ID,
Case When (D.TDATE <= O.DELVDATE) then 1 else 0 end ONTIME,
Case When (D.TDATE > O.DELVDATE) then 1 else 0 end DELAYED
FROM DELIVERY D
JOIN ORDERS O ON D.ORDERID = O.ID
WHERE D.CUSTNO = 327 and D.TNAME = 'A01' and
(EXTRACT(YEAR FROM D.TDATE)= 2023 AND EXTRACT(YEAR FROM O.DELVDATE)= 2023)
)
SELECT COUNT(ID) TOTALDELIVERY, SUM(ONTIME)ONTIME, SUM(DELAYED)DELAYED,
(SUM(ONTIME) / COUNT(ID)) *100 PERCENTAGE
FROM CTE
