I have a Point of Sale system where all checks and tender details exist in a single table. I'm trying to write a SQL query in a way that I can see check totals by tenders to reconcile with cash flow and bank statements. Unfortunately the schema is not mine and can't change it.

One problem I ran into is that there are cases where one check has multiple transactions involving various tenders, therefore I need to do implement (business set) rules to allocate taxes evenly. Those rules might change in the future to say, allocate taxes to CC first if any, so I need to built in some flexibility.

The SQL table looks like this:

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.

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.

CodePudding user response：

I don't know if the logic is right, but it gets you the results you are after:

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;