2.
unknown
sqlserver
a year ago
2.7 kB
2
Indexable
Never
SELECT ftxn.[BrandID], ftxn.[BrandName], ftxn.[Customer_Segment], SUM(ftxn.[QTY]) AS [VOL], SUM(ftxn.[AMT]) AS [AMT] FROM ( SELECT txn.[CustomerID], txn.[BUID], txn.[BranchID], txn.[SKUCode], txn.[QTY], cus.[Member_Number], cus.[Request_No], cus.[Customer_Segment], pm.[SubDeptID], pm.[BrandID], 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 [CUST_ID] get [Customer_Segment] LEFT JOIN (SELECT sbl.[CustomerID], sbl.[Member_Number], crm.[Request_No], csts.[Customer_Segment] FROM [Customer_SBL] sbl LEFT JOIN [CustomerRequest_CRM] crm ON sbl.[Member_Number] = crm.[Member_Number] LEFT JOIN [Customer_Status] csts ON sbl.[Member_Number] = csts.[Customer_Segment]) cus ON txn.[CustomerID] = cus.[CustomerID] -- USE [SKUCode] get [SubDeptID],[BrandID],[BrandName],[SKU_Price] LEFT JOIN [Product_Master] pm ON txn.[SKUCode] = pm.[SKUCode] -- FILTER WHERE cus.[Request_No] IN ('AA1234','BB4421') AND pm.[SubDeptID] = 101 ) ftxn GROUP BY ftxn.[BrandID], ftxn.[BrandName], ftxn.[Customer_Segment] ORDER BY -- FOR SORTED VIEW ftxn.[BrandID], ftxn.[BrandName], ftxn.[Customer_Segment] /* 2 */ SELECT TOP 10 ftxn.[Member_Number], ftxn.[Gender], ftxn.[Age], ftxn.[Top_Rank_2020], SUM(ftxn.[QTY]) AS [TOTAL_SALES_VOL], SUM(ftxn.[AMT]) AS [TOTAL_SALES_AMT] FROM ( SELECT txn.[CustomerID], txn.[TransactionDate], txn.[SKUCode], txn.[QTY], sbl.[Member_Number], sbl.[Gender], sbl.[Age], csts.[Top_Rank_2020], pm.[SKU_Price], txn.[QTY] * pm.[SKU_Price] AS [AMT] -- TXN TABLE FROM (SELECT [CustomerID],[TransactionDate],[SKUCode],[QTY] FROM [Sales_SKU_CDS] UNION SELECT [CustomerID],[TransactionDate],[SKUCode],[QTY] FROM [Sales_SKU_CDS]) txn -- USE txn.[CustomerID] GET [Member_Number],[Gender],[Age] LEFT JOIN [Customer_SBL] sbl ON txn.[CustomerID] = sbl.[CustomerID] -- USE sbl.[Member_Number] GET [Top_Rank_2020] LEFT JOIN [Customer_Status] csts ON sbl.[Member_Number] = csts.[Member_Number] -- USE txn.[SKUCode] GET [SKU_Price] LEFT JOIN [Product_Master] pm ON txn.[SKUCode] = pm.[SKUCode] -- FILTER WHERE txn.[TransactionDate] >= '2021/01/15' AND txn.[TransactionDate] <= '2021/03/18' AND txn.[SKUCode] <> '66666666' ) ftxn GROUP BY ftxn.[Member_Number], ftxn.[Gender], ftxn.[Age], ftxn.[Top_Rank_2020] ORDER BY SUM(ftxn.[AMT]) DESC