SELECT
case when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser is null then '不正常'
when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser ='asuser' then '正常'
when so_d.cSCloser != 'asuser' and so_d.cSCloser is not null then '取消'
end status,
case when COALESCE(so_d.foutquantity,0) = COALESCE(rd32_summary.iQuantity, 0) then '正常'
when COALESCE(so_d.foutquantity,0) != COALESCE(rd32_summary.iQuantity, 0) then '不正常'
end status_2,
so_m.dDate,
so_m.dverifysystime,
so_d.cSCloser,
so_d.AutoID,
so_m.cSOCode,
so_d.iSOsID,
so_d.cInvCode,
so_d.cFree1,
so_d.iQuantity,
so_d.iFHQuantity,
so_d.foutquantity,
so_d.foutnum,
disp_summary.iQuantity disp_qty,
rd32_summary.latest_date latest_date,
rd32_summary.latest_veri_time latest_veri_date,
rd32_summary.c c,
rd32_summary.iQuantity sum_iQuantity,
rd32_summary.iNum sum_iNum,
so_d.iKPQuantity,
so_d.fVeriDispQty,
row_number() over(partition by case when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser is null then '不正常'
when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser ='asuser' then '正常'
end ORDER BY so_d.iSOsID desc) r
--
-- update so_d
-- SET iFHQuantity = disp_summary.iQuantity
-- SET foutquantity = rd32_summary.iQuantity,
-- foutnum = rd32_summary.iNum

FROM [dbo].[SO_SODetails] so_d

LEFT JOIN SO_SOMain so_m
ON so_d.ID = so_m.ID

LEFT JOIN (
SELECT
disp_d.iSOsID,
sum(disp_d.iQuantity) iQuantity

FROM DispatchLists disp_d

LEFT JOIN DispatchList disp_m
ON disp_d.DLID = disp_m.DLID

GROUP BY disp_d.iSOsID
) disp_summary
ON disp_summary.iSOsID = so_d.iSOsID

LEFT JOIN (
SELECT
COALESCE(iorderdid, isodid) isodid,
max(rd32_m.dDate) latest_date,
max(rd32_m.dnverifytime) latest_veri_time,
count(*) c,
sum(iQuantity) iQuantity,
sum(iNum) iNum
FROM rdrecords32 rd32_d

LEFT JOIN rdrecord32 rd32_m
ON rd32_m.ID = rd32_d.ID

GROUP BY  COALESCE(iorderdid, isodid)
) rd32_summary
ON rd32_summary.isodid = so_d.iSOsID

WHERE 1=1
-- AND rd32_summary.latest_date < '2023-03-06'
-- AND so_d.iFHQuantity > 0
-- AND so_d.iQuantity = so_d.iFHQuantity
AND so_d.cSCloser is null
AND so_d.iQuantity = COALESCE(rd32_summary.iQuantity, 0)  -- 已完全出库
-- AND COALESCE(so_d.iFHQuantity,0) != COALESCE(disp_summary.iQuantity, 0)  -- 发货数量不一致
-- AND COALESCE(so_d.foutquantity,0) != COALESCE(rd32_summary.iQuantity, 0)  -- 出库数量不一致


ORDER BY rd32_summary.latest_veri_time