1.1

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