我有一個銷售點系統，其中所有支票和投標詳細資訊都存在于一個表中。我正在嘗試以一種可以查看投標支票總額的方式撰寫 SQL 查詢，以與現金流和銀行對帳單進行核對。不幸的是，架構不是我的，無法更改。
我遇到的一個問題是，在某些情況下，一張支票有多個涉及各種投標的交易，因此我需要執行（業務集）規則以平均分配稅款。這些規則將來可能會改變，如果有的話，首先將稅收分配給 CC，所以我需要建立一些靈活性。
SQL 表如下所示：
| 檢查ID | 線型 | 招標名稱 | 總行 | 稅 |
|---|---|---|---|---|
| 1 | 已售出物品 | 5.00 | 0.25 | |
| 1 | 投標總數 | 現金 | 5.25 | |
| 2 | 已售出物品 | 10.00 | 0.50 | |
| 2 | 投標總數 | 現金 | 5.00 | |
| 2 | 投標總數 | 簽證 | 5.50 | |
| 3 | 已售出物品 | 10.00 | 0.25 | |
| 3 | 已售出物品 | 10.00 | 0.25 | |
| 3 | 投標總數 | 美國運通 | 10.25 | |
| 3 | 投標總數 | 簽證 | 10.25 | |
| 4 | 已售出物品 | 10.00 | 0.50 | |
| 4 | 投標總數 | 現金 | 20.00 | |
| 4 | 投標總數 | 現金 | -9.50 |
The resulting report needs to have one row per tender, with tax equally distributed among check tenders, and net revenue being the difference between total sale and tax.
| TenderName | TotalSale | NetRevenue | TaxCollected |
|---|---|---|---|
| Cash | 20.75 | 19.75 | 1.00 |
| VISA | 15.75 | 15.25 | 0.50 |
| AMEX | 10.25 | 10.00 | 0.25 |
I tried using Select with Exists, also CTE and recursive CTEs, but can't quite figure it out how to do the tax part cleanly. Any other SQL tricks I could try?
We are using SQL Server 2012 at the moment, but have plans in plan to upgrade to 2016 in the near future.
uj5u.com熱心網友回復：
我不知道邏輯是否正確，但它可以讓你得到你想要的結果：
WITH Tenders AS(
SELECT V.CheckID,
V.LineType,
V.TenderName,
V.LineTotal,
SUM(CASE WHEN V.TenderName IS NULL THEN V.Tax END) OVER (PARTITION BY V.CheckID) AS Tax
FROM (VALUES(1,'ItemSold',NULL,5.00,0.25),
(1,'TenderTotal','Cash',5.25,NULL),
(2,'ItemSold',NULL,10.00,0.50),
(2,'TenderTotal','Cash',5.00,NULL),
(2,'TenderTotal','VISA',5.50,NULL),
(3,'ItemSold',NULL,10.00,0.25),
(3,'ItemSold',NULL,10.00,0.25),
(3,'TenderTotal','AMEX',10.25,NULL),
(3,'TenderTotal','VISA',10.25,NULL),
(4,'ItemSold',NULL,10.00,0.50),
(4,'TenderTotal','Cash',20.00,NULL),
(4,'TenderTotal','Cash',-9.50,NULL))V(CheckID,LineType,TenderName,LineTotal,Tax))
SELECT T.TenderName,
SUM(T.LineTotal) AS TotalSale,
SUM(T.LineTotal - T.Tax) AS NetRevenue,
SUM(T.Tax) AS TaxCollected
FROM Tenders T
WHERE T.TenderName IS NOT NULL
GROUP BY T.TenderName;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/452420.html