by darkload [SQL Query] [2018.01.04 10:04:51]
SELECT b.cust_name,c.item_name, A.chgo_date,A.cust_code,A.item_code,A.chgo_qnty,A.chgo_dann,A.chgo_amnt,(chgo_amnt*0.1) as chgo_vats FROM ITEM_CHGO as A with(nolock) LEFT OUTER JOIN CUST_INFO B ON a.cust_code = b.cust_Code left outer join item_info c on a.item_code = c.item_code where c.cust_code = '0009'
-- 1. Union All SELECT b.cust_name , c.item_name , a.chgo_date , a.cust_code , a.item_code , a.chgo_qnty , a.chgo_dann , a.chgo_amnt , a.chgo_amnt * 0.1 AS chgo_vats FROM item_chgo AS a WITH(NOLOCK) LEFT OUTER JOIN cust_info b ON a.cust_code = b.cust_Code LEFT OUTER JOIN item_info c ON a.item_code = c.item_code WHERE a.cust_code = '0009' UNION ALL SELECT null AS cust_name , null AS item_name , null AS chgo_date , null AS cust_code , null AS item_code , null AS chgo_qnty , null AS chgo_dann , SUM(a.chgo_amnt) AS chgo_amnt , SUM(a.chgo_amnt) * 0.1 AS chgo_vats FROM item_chgo AS a WITH(NOLOCK) WHERE a.cust_code = '0009' ; -- 2. Grouping Sets SELECT b.cust_name , c.item_name , a.chgo_date , a.cust_code , a.item_code , a.chgo_qnty , a.chgo_dann , SUM(a.chgo_amnt) AS chgo_amnt , SUM(a.chgo_amnt) * 0.1 AS chgo_vats FROM item_chgo AS a WITH(NOLOCK) LEFT OUTER JOIN cust_info b ON a.cust_code = b.cust_Code LEFT OUTER JOIN item_info c ON a.item_code = c.item_code WHERE a.cust_code = '0009' GROUP BY GROUPING SETS ( ( b.cust_name , c.item_name , a.chgo_date , a.cust_code , a.item_code , a.chgo_qnty , a.chgo_dann ) , () ) ;