Advertisement
Pandaaaa906

Untitled

Mar 22nd, 2023
1,438
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.05 KB | None | 0 0
  1. SELECT
  2. *
  3. FROM (
  4. SELECT
  5. case when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser is null then '不正常'
  6. when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser ='asuser' then '正常'
  7. when so_d.cSCloser != 'asuser' and so_d.cSCloser is not null then '取消'
  8. end status,
  9. case when COALESCE(so_d.foutquantity,0) = COALESCE(rd32_summary.iQuantity, 0) then '正常'
  10. when COALESCE(so_d.foutquantity,0) != COALESCE(rd32_summary.iQuantity, 0) then '不正常'
  11. end status_2,
  12. so_m.cSOCode,
  13. so_m.dDate,
  14. so_m.dverifysystime,
  15. so_d.cSCloser,
  16. so_d.AutoID,
  17. so_d.iSOsID,
  18. so_d.cInvCode,
  19. so_d.cFree1,
  20. so_d.iQuantity,
  21. so_d.iFHQuantity,
  22. so_d.foutquantity,
  23. so_d.foutnum,
  24. rd32_summary.latest_date latest_date,
  25. rd32_summary.latest_veri_time latest_veri_date,
  26. rd32_summary.c c,
  27. rd32_summary.iQuantity sum_iQuantity,
  28. rd32_summary.iNum sum_iNum,
  29. so_d.iKPQuantity,
  30. so_d.fVeriDispQty,
  31. row_number() over(partition by case when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser is null then '不正常'
  32. when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser ='asuser' then '正常'
  33. end ORDER BY iSOsID desc) r
  34. --
  35. -- update so_d
  36. -- SET foutquantity = rd32_summary.iQuantity,
  37. -- foutnum = rd32_summary.iNum
  38.  
  39. FROM [dbo].[SO_SODetails] so_d
  40.  
  41. LEFT JOIN SO_SOMain so_m
  42. ON so_d.ID = so_m.ID
  43.  
  44. LEFT JOIN (
  45. SELECT
  46. COALESCE(iorderdid, isodid) isodid,
  47. max(rd32_m.dDate) latest_date,
  48. max(rd32_m.dnverifytime) latest_veri_time,
  49. count(*) c,
  50. sum(iQuantity) iQuantity,
  51. sum(iNum) iNum
  52. FROM rdrecords32 rd32_d
  53.  
  54. LEFT JOIN rdrecord32 rd32_m
  55. ON rd32_m.ID = rd32_d.ID
  56.  
  57. GROUP BY  COALESCE(iorderdid, isodid)
  58. ) rd32_summary
  59. ON rd32_summary.isodid = so_d.iSOsID
  60.  
  61. WHERE 1=1
  62. -- AND rd32_summary.latest_date < '2023-03-06'
  63. AND so_d.iFHQuantity > 0
  64. -- AND so_d.iQuantity = so_d.iFHQuantity
  65. AND so_d.cSCloser is null
  66. AND COALESCE(so_d.foutquantity,0) != COALESCE(rd32_summary.iQuantity, 0)
  67. ) tmp
  68. where 1=1
  69. -- 查看不正常数据
  70. -- AND (
  71. -- status='不正常'
  72. -- OR status_2='不正常'
  73. -- )
  74. AND status_2='不正常'
  75. -- AND tmp.r < 10
  76.  
  77. ORDER BY tmp.latest_veri_date
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement