I have this stored procedure shown here which calculates the order qty, rejection and percentage for rejection against each department, but I have an issue: for summing the order qty, I need to consider only distinct order number to sum the order qty, but for rejection qty all rows should be considered. How to achieve this?
Stored procedure:
CREATE PROCEDURE orderqtyper
@fromDate Date,
@toDate Date
AS
SELECT
Department,
SUM(Order_Qty) AS [Order Qty],
SUM(Rejection_Qty) AS [Rejection Qty],
FORMAT((SUM(Rejection_Qty) * 100.0 / NULLIF(SUM(Order_Qty), 0) / 100), 'P') AS Percentage
FROM
Semicon_NPD
WHERE
(Date BETWEEN @fromDate AND @toDate)
GROUP BY
Department
ORDER BY
Percentage DESC
Sample table:
Current result:
Expected result (if the the order number is the same, it should take sum of only unique values for order qty but sum of all for rejections qty):
sql insert statement:
CREATE TABLE mytable(
ID INTEGER NOT NULL PRIMARY KEY
,Date VARCHAR(10) NOT NULL
,MRP VARCHAR(7) NOT NULL
,Material_Number INTEGER
,Material_Type VARCHAR(5)
,Weight_in_Kg NUMERIC(5,3)
,Order_Number INTEGER
,Order_Qty INTEGER
,Rejection_Qty INTEGER
,Department VARCHAR(12)
,Reason_Brief VARCHAR(17)
,Reason_Explained VARCHAR(57)
,Responsibility_1 VARCHAR(8)
,Responsibility_2 VARCHAR(1)
,Responsibility_3 VARCHAR(6)
,Responsibility_4 VARCHAR(6)
,Corrective_Action VARCHAR(58)
,Action_Taken VARCHAR(10)
,Action_Status VARCHAR(12)
);
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (315,'4/5/2021','901',ABC,'AB',0.035,3847478,278,1,'EDM','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (314,'4/5/2021','9',DEF,'bd',0.045,3942733,17,1,'Milling','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (317,'4/5/2021','9',GHI,'AB',0.302,3942733,17,2,'Milling','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (319,'4/5/2021','9',JKL,'bd',0.154,3957917,60,1,'Milling','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (320,'4/5/2021','9',MNO,'AB',0.415,3962344,10,10,'Pre-Material','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (316,'4/5/2021','9',PQR,'bd',0.016,3930483,427,2,'Turning','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (318,'4/5/2021','9',TST,'AB',0.026,3953441,197,2,'Turning','AB','AB','AB','AB','AB','AB','AB','AB','AB');
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (318,'4/5/2021','9',UVW,'bd',0.026,3953441,197,5,'Turning','AB','AB','AB','AB','AB','AB','AB','AB','AB');