Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- COUNT(DISTINCT d.PONum) AS POCount,
- ISNULL(SUM(d.DocExtCost), 0) AS POValue,
- CASE v.Buyer_c
- WHEN 'Person5'
- THEN 'Person4'
- ELSE v.Buyer_c
- END AS BuyerID
- FROM
- [Dbo].PODetail AS d
- INNER JOIN
- [Dbo].Vendor AS v ON (d.Company = v.Company AND d.VendorNum = v.VendorNum)
- WHERE
- v.Buyer_c IN ('Person1', 'Person2', 'Person3', 'Person4', 'Person5')
- GROUP BY
- CASE v.Buyer_c
- WHEN 'Person5'
- THEN 'Person4'
- ELSE v.Buyer_c
- END
- ORDER BY
- v.Buyer_c <-- This order by doesn't work
- GROUP BY
- CASE v.Buyer_c
- WHEN 'Person5'
- THEN 'Person4'
- ELSE v.Buyer_c
- END,
- v.Buyer_c
- with cte as
- (
- select count(distinct d.ponum) as pocount,
- isnull(sum(d.docextcost), 0) as povalue,
- case v.buyer_c
- when 'person5' then 'person4'
- else v.buyer_c
- end as buyerid
- from [dbo].podetail as d
- inner join [dbo].vendor as v on (d.company = v.company and d.vendornum = v.vendornum)
- where v.buyer_c in ('person1', 'person2', 'person3', 'person4', 'person5')
- group by case v.buyer_c
- when 'person5' then 'person4'
- else v.buyer_c
- end
- )
- select *
- from cte
- order by buyerid
- SELECT COUNT(DISTINCT d.PONum) AS POCount,
- ISNULL(SUM(d.DocExtCost), 0) AS POValue,
- CASE v.Buyer_c
- WHEN 'Person5' THEN 'Person4'
- ELSE v.Buyer_c
- END AS BuyerID
- FROM [Dbo].PODetail AS d
- INNER JOIN [Dbo].Vendor AS v
- ON (d.Company = v.Company AND
- d.VendorNum = v.VendorNum)
- WHERE v.Buyer_c IN ('Person1', 'Person2', 'Person3', 'Person4', 'Person5')
- GROUP BY CASE v.Buyer_c
- WHEN 'Person5' THEN 'Person4'
- ELSE v.Buyer_c
- END
- ORDER BY CASE v.Buyer_c
- WHEN 'Person5' THEN 'Person4'
- ELSE v.Buyer_c
- END
Add Comment
Please, Sign In to add comment