1.1
unknown
sqlserver
2 years ago
1.2 kB
7
Indexable
Never
SELECT ftxn.[Region_Group], ftxn.[GroupBranch], SUM(ftxn.[QTY]) AS [VOL], SUM(ftxn.[AMT]) AS [AMT] FROM (SELECT txn.[CustomerID], txn.[BUID], txn.[BranchID], txn.[SKUCode], txn.[QTY], brn.[Region_Group], brn.[GroupBranch], pm.[SKU_Price], txn.[QTY] * pm.[SKU_Price] AS [AMT] -- TXN TABLE FROM (SELECT [CustomerID],[BUID],[BranchID],[SKUCode],[QTY] FROM [Sales_SKU_CDS] UNION SELECT [CustomerID],[BUID],[BranchID],[SKUCode],[QTY] FROM [Sales_SKU_CDS]) txn -- USE [BUID],[BranchID] GET [GroupBranch],[Region_Group] LEFT JOIN [Branch_LT] brn ON txn.[BUID] = brn.[BUID] AND txn.[BranchID] = brn.[BranchID] -- USE [SKUCode] GET [SKU_Price] LEFT JOIN [Product_Master] pm ON txn.[SKUCode] = pm.[SKUCode] -- USE [CustomerID] GET [Request_No] LEFT JOIN (SELECT sbl.[CustomerID], sbl.[Member_Number], crm.[Request_No] FROM [Customer_SBL] sbl LEFT JOIN [CustomerRequest_CRM] crm ON sbl.[Member_Number] = crm.[Member_Number]) cus ON txn.[CustomerID] = cus.[CustomerID] -- FILTER WHERE cus.[Request_No] IN ('AA1234','BB4421') ) ftxn GROUP BY ftxn.[Region_Group], ftxn.[GroupBranch] ORDER BY SUM(ftxn.[AMT]) DESC