Need Help for getting specific data

Other SQL Server Topics
1

I have created three column with case function . one is full delivery, another is partial and last one is no delivery. I got the correct result for full and no delivery. But the problem is when i got result for partial delivery it also included no delivery results . Can anyone tell me what is the right query for getting only partial delivery

With Without_Duplicate As
(select ordernumber , supplname, supplemail, sku,orderqty,
Qtyreceived, estimateddelivery,
createemp, status ,
ROW_NUMBER() over (partition by ordernumber order by estimateddelivery desc)
As Rn from analytics.SupplierOrders)

SELECT supplname, estimateddelivery, DATE_DIFF(CAST (CURRENT_DATE AS DATE), CAST ((estimateddelivery) AS DATE), DAY ) AS exceed_days ,
createemp, status ,COUNT (ordernumber) as total_order_from_supplier,

SUM (CASE WHEN qtyreceived = orderqty THEN 1
Else 0
End ) as Full_delivery,
SUM(Case WHEN qtyreceived < orderqty THEN 1
else 0
End ) as Partial_delivery,

SUM(Case WHEN qtyreceived = 0 THEN 1
else 0
End ) as N0_delivery
from Without_Duplicate
Where Rn = 1 and
status = "BES"
GROUP by supplname, estimateddelivery, createemp, status

2

Your Code Formatted

;WITH partial_delivery AS
(
       SELECT supplname,
              safe_cast( "qtyreceived!= 0 "       as int) AS par_deliver,
              safe_cast("qtyreceived != orderqty" AS int) AS n_delivery,
              safe_cast("qtyreceived < orderqty"  AS int) AS full_deliver
       FROM   supplierorders)
SELECT   supplname,
         sum (
         CASE
                  WHEN qtyreceived = orderqty THEN 1
                  ELSE 0
         END ) AS full_delivery,
         sum(
         CASE
                  WHEN qtyreceived IN
                           (
                                  SELECT par_deliver,
                                         n_delivery,
                                         full_deliver
                                  FROM   partial_delivery) THEN 1
                  ELSE 0
         END ) AS partial_delivery,
         sum(
         CASE
                  WHEN qtyreceived = 0 THEN 1
                  ELSE 0
         END ) AS n0_delivery
FROM     supplierorders
GROUP BY supplname