01.
02.SELECT QR_FIRST.*
03. ,row_number()
04. OVER (ORDER BY QR_FIRST.Item_code
05. ,QR_FIRST.total ,QR_FIRST.customer_code) AS dynamic_id
06.FROM
07.(
08. SELECT a.CustomerCode as customer_code
09. ,b.Itemcode as Item_code, sum(b.qtyorder) AS total
10. FROM SO a INNER JOIN SO_Detail b
11. ON a.SOID = b.SOID
12. GROUP BY b.Itemcode ,a.CustomerCode
13.) AS QR_FIRST
14.ORDER BY QR_FIRST.Item_code ,QR_FIRST.total ,QR_FIRST.customer_code
15.
16.
17.View นี้ให้ชื่อว่า ViewGetMaxItem ค่ะ
18.และที่ Command จะเขียนดังนี้ ( ไม่ต้องการ Field ไหนก็ลบออกนะคะ )
19.
20.SELECT
21. CASE
22. WHEN QR_MAX.first_rec_id = QR_CUSTOMER.dynamic_id
23. THEN CONVERT(VARCHAR(50) ,QR_MAX.item_code)
24. ELSE ''
25. END AS item_code_label
26. ,QR_CUSTOMER.item_code,QR_CUSTOMER.CUSTOMER_CODE as custmain
27. ,QR_MAX.max_item ,QR_MAX.total_customer
28. ,CASE
29. WHEN QR_MAX.first_rec_id = QR_CUSTOMER.dynamic_id
30. THEN QR_MAX.max_item
31. ELSE 0
32. END AS filter_max_item
33. ,CASE
34. WHEN QR_MAX.first_rec_id = QR_CUSTOMER.dynamic_id
35. THEN QR_MAX.total_customer
36. ELSE 0
37. END AS filter_total_customer
38. ,CASE
39. WHEN QR_MAX.first_rec_id = QR_CUSTOMER.dynamic_id
40. THEN ''
41. ELSE 'Not include by same maximum order'
42. END AS comment
43.FROM dbo.ViewGetMaxItem AS QR_CUSTOMER
44. RIGHT JOIN
45. (
46. SELECT QR_INNER.item_code, MAX(QR_INNER.total) AS max_item
47. ,COUNT(QR_INNER.customer_code) AS total_customer
48. ,MIN(QR_INNER.dynamic_id) as first_rec_id
49. FROM dbo.ViewGetMaxItem AS QR_INNER
50. GROUP BY QR_INNER.item_code
51. ) AS QR_MAX
52. ON (QR_CUSTOMER.item_code=QR_MAX.item_code)
53. AND (QR_CUSTOMER.total = QR_MAX.max_item)
54.ORDER BY QR_CUSTOMER.item_code ,QR_CUSTOMER.CUSTOMER_CODE;
