Guest User

Untitled

a guest
Mar 22nd, 2018
77
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.97 KB | None | 0 0
  1. SELECT
  2. COUNT(DISTINCT d.PONum) AS POCount,
  3. ISNULL(SUM(d.DocExtCost), 0) AS POValue,
  4. CASE v.Buyer_c
  5. WHEN 'Person5'
  6. THEN 'Person4'
  7. ELSE v.Buyer_c
  8. END AS BuyerID
  9. FROM
  10. [Dbo].PODetail AS d
  11. INNER JOIN
  12. [Dbo].Vendor AS v ON (d.Company = v.Company AND d.VendorNum = v.VendorNum)
  13. WHERE
  14. v.Buyer_c IN ('Person1', 'Person2', 'Person3', 'Person4', 'Person5')
  15. GROUP BY
  16. CASE v.Buyer_c
  17. WHEN 'Person5'
  18. THEN 'Person4'
  19. ELSE v.Buyer_c
  20. END
  21. ORDER BY
  22. v.Buyer_c <-- This order by doesn't work
  23.  
  24. GROUP BY
  25. CASE v.Buyer_c
  26. WHEN 'Person5'
  27. THEN 'Person4'
  28. ELSE v.Buyer_c
  29. END,
  30. v.Buyer_c
  31.  
  32. with cte as
  33. (
  34. select count(distinct d.ponum) as pocount,
  35. isnull(sum(d.docextcost), 0) as povalue,
  36. case v.buyer_c
  37. when 'person5' then 'person4'
  38. else v.buyer_c
  39. end as buyerid
  40. from [dbo].podetail as d
  41. inner join [dbo].vendor as v on (d.company = v.company and d.vendornum = v.vendornum)
  42. where v.buyer_c in ('person1', 'person2', 'person3', 'person4', 'person5')
  43. group by case v.buyer_c
  44. when 'person5' then 'person4'
  45. else v.buyer_c
  46. end
  47. )
  48. select *
  49. from cte
  50. order by buyerid
  51.  
  52. SELECT COUNT(DISTINCT d.PONum) AS POCount,
  53. ISNULL(SUM(d.DocExtCost), 0) AS POValue,
  54. CASE v.Buyer_c
  55. WHEN 'Person5' THEN 'Person4'
  56. ELSE v.Buyer_c
  57. END AS BuyerID
  58.  
  59. FROM [Dbo].PODetail AS d
  60.  
  61. INNER JOIN [Dbo].Vendor AS v
  62. ON (d.Company = v.Company AND
  63. d.VendorNum = v.VendorNum)
  64.  
  65. WHERE v.Buyer_c IN ('Person1', 'Person2', 'Person3', 'Person4', 'Person5')
  66.  
  67. GROUP BY CASE v.Buyer_c
  68. WHEN 'Person5' THEN 'Person4'
  69. ELSE v.Buyer_c
  70. END
  71.  
  72. ORDER BY CASE v.Buyer_c
  73. WHEN 'Person5' THEN 'Person4'
  74. ELSE v.Buyer_c
  75. END
Add Comment
Please, Sign In to add comment