nord vpnnord vpn
Ad

2.

mail@pastecode.io avatar
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

nord vpnnord vpn
Ad