0

I'm trying to make this business logic make sense, but I'm not having any luck.
The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after discounted percentage, from the sequence column.

If this was only about percentages, I could make it work, the problem is the case in the middle when in the same sale there's a value to subtract from Current value.

+--------+-----------+------+----+---+
| SaleId | Discount  | Type | S  | q |
+--------+-----------+------+----+---+
|      1 |      0.15 | P    | rc | 1 |
|      1 |     10.05 | V    | l  | 2 |
|      2 |      0.05 | P    | rc | 1 |
|      3 |      0.05 | P    | rc | 1 |
|      3 |      8.00 | V    | l  | 2 |
|      3 |      0.25 | P    | rc | 3 |
+--------+-----------+------+----+---+

+--------+--------+
| SaleId | Amount |
+--------+--------+
|      1 | 100.00 |
|      2 | 200.00 |
|      3 |  50.00 |
+--------+--------+


+--------+--------+----------+------+-----+--------+--------------+
| SaleId | Amount | Discount | Type | Seq | T_Disc | CurrentValue |
+--------+--------+----------+------+-----+--------+--------------+
|      1 |    100 |     0,15 | Perc |   1 |   0,85 |           85 |
|      1 |    100 |    10,05 | Val  |   2 |      0 |        74,95 |
|      2 |    200 |     0,05 | Perc |   1 |   0,95 |          190 |
|      3 |     50 |      0,1 | Perc |   1 |   0,95 |           45 |
|      3 |     50 |        8 | Val  |   2 |      0 |           37 |
|      3 |     50 |     0,25 | Perc |   3 |      0 |        27,75 |
+--------+--------+----------+------+-----+--------+--------------+

Sale 3:

  • 50 minus 10%
  • 45 minus 8
  • 37 minus 25%

After this I need to display the last row from each SaleId.

Can you please advise if this is possible?

I'm trying CROSS APPLY and a CTE (WITH clause):

WITH CTE_CalcTotalDisc
AS 
(
    Select A.SaleId
    ,CONVERT(DECIMAL(8,3),B.Amount) Amount
    ,Discount
    ,Tipo
    ,A.Seq
    ,CASE WHEN Tipo='Val' THEN convert(decimal(8,3),Discount)
        ELSE convert(decimal(8,3),1-(1*Discount))END AS T_Disc
    ,CASE WHEN Tipo='Val' THEN convert(decimal(8,3),Amount-Discount)
          WHEN Tipo='Perc' THEN convert(decimal(8,3),Amount-(Amount*Discount))
        ELSE convert(decimal(8,3),0.00) --convert(decimal(6,5),1-(1*Discount))
        END 
    AS CurrentValue
    From #disc A, #Sales B
    where Seq=1 AND A.SaleId=B.SaleId

    UNION ALL

    Select d.SaleId
    ,CONVERT(DECIMAL(8,3),0.00) 
    ,d.Discount
    ,d.Tipo
    ,d.Seq
    --,convert(decimal(6,5),(1-(1*d.Discount))*T_Disc) T_Disc
    --,CASE WHEN d.Tipo='Val' THEN d.Discount ELSE convert(decimal(6,5),1-(1*d.Discount))END 
    , CONVERT(DECIMAL(8,3),0.000) AS T_Disc
    ,CONVERT(DECIMAL(8,3),0.000) AS CurrentValue
    From #disc d --, #Sales B
        JOIN CTE_CalcTotalDisc ad on d.SaleId=ad.SaleId
                            AND d.Seq=ad.Seq+1
)
SELECT * FROM CTE_CalcTotalDisc I1
CROSS APPLY(
  SELECT 
  --SUM(CurrentValue)   RunningTotal
  I1.CurrentValue-(I1.CurrentValue*I1.Discount)   RunningTotal
  FROM CTE_CalcTotalDisc I2
  WHERE I1.SALEID = I2.SALEID
  --AND I1.Tipo = I2.Tipo
  AND I1.Seq <= I2.Seq
)C

closed as unclear what you're asking by Tom V - Team Monica, Philᵀᴹ, Erik, RLF, Andriy M Nov 15 '16 at 19:38

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Hi there, welcome to the site. What RDBMS are you using? – Tom V - Team Monica Nov 14 '16 at 19:42
  • Also, I tried formatting your tables, If I messed up at some point please redo it yourself. – Tom V - Team Monica Nov 14 '16 at 19:47
  • Your query seems to suggest you are using SQL Server and I've tagged your question as such, but please specify the version of your SQL Server instance as well. Thank you. – Andriy M Nov 15 '16 at 17:07
1

The following will work on 2012+. Note that there were a couple of inconsistencies between your supplied SQL and the sample schema that you provided and I think I have followed the names in your SQL.

With CTE_CalcTotalDisc As (
    Select d.SaleID, s.Amount, d.Discount, d.Tipo, d.Seq,
        T_Disc = iif(d.Tipo = 'V', d.Discount, (s.Amount * d.Discount)),
        CurrentValue = iif(d.Tipo = 'V', s.Amount - d.Discount, iif(d.Tipo = 'P', s.Amount - (s.Amount * d.Discount), 0))
      From #disc As d
      Join #sales As s On d.SaleID = s.SaleID
      Where d.Seq = 1
    Union All
    Select d.SaleID, Amount = cte.CurrentValue, d.Discount, d.Tipo, d.Seq,
        T_Disc = iif(d.Tipo = 'V', d.Discount, (cte.CurrentValue * d.Discount)),
        CurrentValue = iif(d.Tipo = 'V', cte.CurrentValue - d.Discount, iif(d.Tipo = 'P', cte.CurrentValue - (cte.CurrentValue * d.Discount), 0))
      From #disc As d
      Join CTE_CalcTotalDisc As cte
        On d.SaleID = cte.SaleID
        And d.Seq = cte.Seq + 1),
CTE_Ordered As (
    Select SaleID, CurrentValue, 
        Discount = Sum(T_Disc) Over (Partition By SaleID Order By Seq Asc), 
        RowNo = Row_Number() Over (Partition By SaleID Order By Seq Desc)
      From CTE_CalcTotalDisc)
--Select * From CTE_CalcTotalDisc
Select * From CTE_Ordered Where RowNo = 1;

Note the use of Row_Number(), partitioned by SaleID and sorted descending by Seq to get the final value, and the use of Sum() to get the running total of the discount.

You can uncomment the commented select statement to view all rows, not just the final row for each SaleID.

Not the answer you're looking for? Browse other questions tagged or ask your own question.