CODEWITHSUNDEEP
sqlsql-server
Megan2906
Megan2906

Reputation: 11

SQL Reduce a value multiple times

I have a value from a table that I want to reduce for each percentage e.g.

Table 1

ID    Percentage    FKID
1       5             1
2      10             1
3       4             1

Table 2

ID  AMOUNT
1   100

Declare @qty decimal

Select @qty = amount
From table2 where id = 1

Select @qty = @qty – @qty/100 * percentage
From Table1

I would expect 5% of 100, 10% of 95 then, 4% of 85.5. So my overall total would be 82.1 but I keep getting % of the amount, so 5% of 100, 10% off 100 and 4% off 100.

Is there a way of doing what I am trying to achieve?

Upvotes: 1

Views: 508

Answers (3)

LukStorms
LukStorms

Reputation: 29667

If the goal is to merely calculate a variable for a given Table2.ID ?

Then that method you used can work for it.

But tables contain unordered sets.
So you need to include an ORDER BY to respect the order that the percentages are subtracted.

Sample data:

CREATE TABLE Table2 (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Amount INT
);

INSERT INTO Table2 (Amount) VALUES
  (100),(100)
;

CREATE TABLE Table1 (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Percentage INT NOT NULL,
  FKID INT NOT NULL,
  FOREIGN KEY (FKID) REFERENCES Table2(ID)
);

INSERT INTO Table1
(Percentage, FKID) VALUES
 (5,1), (10,1), (4,1),
 (30,2), (25,2), (20,2)

T-Sql:

DECLARE @Id INT = 1;

DECLARE @qty FLOAT;
SELECT @qty = Amount
FROM Table2
WHERE ID = @Id;

SELECT 
@qty = @qty-((@qty/100.0)*t1.Percentage)
FROM Table1 t1
WHERE t1.FKID = @Id
ORDER BY t1.ID;

SELECT CAST(@qty AS DECIMAL(16,2)) AS qty

| qty   |
| :---- |
| 82.08 |

A test on db<>fiddle here

Upvotes: 0

TomNash
TomNash

Reputation: 3288

You're looking for the result of this expression since it doesn't matter the order of the multiplication.

100 * (0.95 * 0.9 * 0.96)

You can emulate chaining the products using LOG, SUM, and EXP to get the desired result

SELECT
  val.ID, EXP(SUM(LOG(0.01*(100-Percentage)))) * MAX(Amount)
FROM
  percents
    INNER JOIN
  val
    ON percents.FKID = val.ID
GROUP BY
  val.ID;

http://sqlfiddle.com/#!18/60c27/1

Upvotes: 1

Thom A
Thom A

Reputation: 95588

The only way (unfortunately) you can do this is iteratively; which means using an rCTE. Assuming that Percentage can't be a negative value:

CREATE TABLE dbo.Table1 (ID int,
                         [Percentage] int, --Ideally you should store percentages as a decimal, that is what that are after all. E.g. 10% = 0.10.
                         FKID int);

CREATE TABLE dbo.Table2 (ID int,
                         Amount decimal(5, 1));
GO

INSERT INTO dbo.Table1 (ID,
                        [Percentage],
                        FKID)
VALUES (1, 5, 1),
       (2, 10, 1),
       (3, 4, 1);
GO

INSERT INTO dbo.Table2 (ID,
                        Amount)
VALUES (1, 100);
GO

SELECT T2.ID AS ID2,
       T1.ID AS ID1,
       CONVERT(decimal(6, 2), T2.Amount * ((100 - T1.[Percentage]) / 100.0)) AS Amount
FROM dbo.Table2 T2
     JOIN dbo.Table1 T1 ON T2.ID = T1.ID
WHERE T1.ID = 1;
GO

WITH rCTE AS
    (SELECT T2.ID AS ID2,
            T1.ID AS ID1,
            CONVERT(decimal(6, 2), T2.Amount * ((100 - T1.[Percentage]) / 100.0)) AS Amount
     FROM dbo.Table2 T2
          JOIN dbo.Table1 T1 ON T2.ID = T1.ID
     WHERE T1.ID = 1
     UNION ALL
     SELECT r.ID2,
            T1.ID,
            CONVERT(decimal(6, 2), r.Amount * ((100 - T1.[Percentage]) / 100.0))
     FROM rCTE r
          JOIN dbo.Table1 T1 ON r.ID2 = T1.FKID
                            AND r.ID1 + 1 = T1.ID)
SELECT MIN(r.Amount)
FROM rCTE r
GROUP BY r.ID2;

GO
DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;

Upvotes: 1

Related Questions