Very Computer

by **Alan Perkin** » Mon, 15 Apr 2002 16:02:51

Simplified DDL:

CREATE TABLE #InvoiceItems (
InvoiceItemID int NOT NULL ,
InvoiceID int NULL ,
Quantity float NULL ,
UnitPrice money NULL ,
InventoryId int NULL
)

CREATE TABLE #InvoiceItemDiscounts (
InvoiceItemDiscountID int NOT NULL ,
InvoiceItemID int NOT NULL ,
DiscountTypeID int NOT NULL ,
DiscountRate float NOT NULL ,
DiscountAmount money NOT NULL,
)

CREATE TABLE #DiscountTypes (
DiscountTypeID int NOT NULL ,
Description nvarchar(80),
CalcOnBal bit NOT NULL ,
Sequence int NULL
)

INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
Sequence) VALUES (1, 'Volume Discount', 1, 2)
INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
Sequence) VALUES (2, 'Education Discount', 1, 1)
INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
Sequence) VALUES (3, 'Reseller Discount', 0, 3)
INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
Sequence) VALUES (2, 'Amount', 1, 4)

INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity, UnitPrice,
InventoryId) VALUES (1, 1, 1, 1000, 1)
INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity, UnitPrice,
InventoryId) VALUES (2, 1, 1, 2000, 3)
INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity, UnitPrice,
InventoryId) VALUES (3, 1, 2, 4000, 6)

INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
DiscountTypeID, DiscountRate, DiscountAmount) VALUES (1, 1, 1, 0.1, 0)
INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
DiscountTypeID, DiscountRate, DiscountAmount) VALUES (2, 1, 2, 0.3, 0)
INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
DiscountTypeID, DiscountRate, DiscountAmount) VALUES (3, 1, 4, 0, 100)
INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
DiscountTypeID, DiscountRate, DiscountAmount) VALUES (4, 2, 3, 0.3, 0)

The Problem:

I am trying to calculate for each line on an invoice the value after
applying discounts. The base calculation is InvoiceItems.Quantity *
InvoiceItems.UnitPriceEach. From that starting point, I have to apply
discounts in the DiscountTypes.Sequence Order - discount rate is either
calculated on the running balance (DiscountTypes.CalcOnBal = 1) or on the
total value. Discount Amounts are also applied in the order of the
DiscountType.Sequence.

10% and 20% respectively (in that order), both calconbal = 1, then I want to
end up with 3000 - 300 = 2700 - 540 = 2160.

Any help is greatly appreciated.

Alan Perkins

by **Itzik Ben-Ga** » Mon, 15 Apr 2002 16:23:21

Alan,

Thanks for the DDL and sample data.

Can you please also specify PKs and FKs, plus provide the desired output?

Thanks,
--
BG

SQL Server MVP
Hi-Tech College, Israel
http://sql.hi-tech.co.il
http://www.tsqlsolutions.com
http://www.sqlmag.com

> Simplified DDL:

> CREATE TABLE #InvoiceItems (
> InvoiceItemID int NOT NULL ,
> InvoiceID int NULL ,
> Quantity float NULL ,
> UnitPrice money NULL ,
> InventoryId int NULL
> )

> CREATE TABLE #InvoiceItemDiscounts (
> InvoiceItemDiscountID int NOT NULL ,
> InvoiceItemID int NOT NULL ,
> DiscountTypeID int NOT NULL ,
> DiscountRate float NOT NULL ,
> DiscountAmount money NOT NULL,
> )

> CREATE TABLE #DiscountTypes (
> DiscountTypeID int NOT NULL ,
> Description nvarchar(80),
> CalcOnBal bit NOT NULL ,
> Sequence int NULL
> )

> INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> Sequence) VALUES (1, 'Volume Discount', 1, 2)
> INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> Sequence) VALUES (2, 'Education Discount', 1, 1)
> INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> Sequence) VALUES (3, 'Reseller Discount', 0, 3)
> INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> Sequence) VALUES (2, 'Amount', 1, 4)

> INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity, UnitPrice,
> InventoryId) VALUES (1, 1, 1, 1000, 1)
> INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity, UnitPrice,
> InventoryId) VALUES (2, 1, 1, 2000, 3)
> INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity, UnitPrice,
> InventoryId) VALUES (3, 1, 2, 4000, 6)

> INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> DiscountTypeID, DiscountRate, DiscountAmount) VALUES (1, 1, 1, 0.1, 0)
> INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> DiscountTypeID, DiscountRate, DiscountAmount) VALUES (2, 1, 2, 0.3, 0)
> INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> DiscountTypeID, DiscountRate, DiscountAmount) VALUES (3, 1, 4, 0, 100)
> INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> DiscountTypeID, DiscountRate, DiscountAmount) VALUES (4, 2, 3, 0.3, 0)

> The Problem:

> I am trying to calculate for each line on an invoice the value after
> applying discounts. The base calculation is InvoiceItems.Quantity *
> InvoiceItems.UnitPriceEach. From that starting point, I have to apply
> discounts in the DiscountTypes.Sequence Order - discount rate is either
> calculated on the running balance (DiscountTypes.CalcOnBal = 1) or on the
> total value. Discount Amounts are also applied in the order of the
> DiscountType.Sequence.

of
> 10% and 20% respectively (in that order), both calconbal = 1, then I want
to
> end up with 3000 - 300 = 2700 - 540 = 2160.

> Any help is greatly appreciated.

> Alan Perkins

by **Alan Perkin** » Mon, 15 Apr 2002 16:39:30

Keys:

#InvoiceItems.InvoiceItemID:

CONSTRAINT [aaaaaInvoiceItems_PK] PRIMARY KEY NONCLUSTERED
(
[InvoiceItemID]
) WITH FILLFACTOR = 70 ON [PRIMARY]

#InvoiceItemDiscounts:

CONSTRAINT [InvoiceItemDiscounts_PK] PRIMARY KEY CLUSTERED
(
[InvoiceItemDiscountID]
) ON [PRIMARY] ,
CONSTRAINT [DiscountTypes_InvoiceItemDiscounts_FK1] FOREIGN KEY
(
[DiscountTypeID]
) REFERENCES [DiscountTypes] (
[DiscountTypeID]
),
CONSTRAINT [InvoiceItems_InvoiceItemDiscounts_FK1] FOREIGN KEY
(
[InvoiceItemID]
) REFERENCES [InvoiceItems] (
[InvoiceItemID]
) ON DELETE CASCADE

#DiscountTypes:

CONSTRAINT [DiscountTypes_PK] PRIMARY KEY CLUSTERED
(
[DiscountTypeID]
) ON [PRIMARY]

Thus InvoiceItems has a 1 to many relationship with InvoiceItemDiscounts
(InvoiceItems INNER JOIN InvoiceItemDiscounts ON InvoiceItems.InvoiceItemID
= InvoiceItemDiscounts.InvoiceItemID)

InvoiceItemDiscounts has a 1 to many relationship with DiscountTypes
InvoiceItemDiscounts.DiscountTypeID = DiscountTypes.DiscountTypeID

The result I am after is the net value for each InvoiceItems.InvoiceItemID
of (Quantity * UnitPrice) less any applicable discounts. From there I will
apply the total to invoices as a whole, but that is beyond the scope of the
problem.

Thanks again for any help.

> Alan,

> Thanks for the DDL and sample data.

> Can you please also specify PKs and FKs, plus provide the desired output?

> Thanks,
> --
> BG

> SQL Server MVP
> Hi-Tech College, Israel
> http://sql.hi-tech.co.il
> http://www.tsqlsolutions.com
> http://www.sqlmag.com

> > Simplified DDL:

> > CREATE TABLE #InvoiceItems (
> > InvoiceItemID int NOT NULL ,
> > InvoiceID int NULL ,
> > Quantity float NULL ,
> > UnitPrice money NULL ,
> > InventoryId int NULL
> > )

> > CREATE TABLE #InvoiceItemDiscounts (
> > InvoiceItemDiscountID int NOT NULL ,
> > InvoiceItemID int NOT NULL ,
> > DiscountTypeID int NOT NULL ,
> > DiscountRate float NOT NULL ,
> > DiscountAmount money NOT NULL,
> > )

> > CREATE TABLE #DiscountTypes (
> > DiscountTypeID int NOT NULL ,
> > Description nvarchar(80),
> > CalcOnBal bit NOT NULL ,
> > Sequence int NULL
> > )

> > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > Sequence) VALUES (1, 'Volume Discount', 1, 2)
> > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > Sequence) VALUES (2, 'Education Discount', 1, 1)
> > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > Sequence) VALUES (3, 'Reseller Discount', 0, 3)
> > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > Sequence) VALUES (2, 'Amount', 1, 4)

> > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
UnitPrice,
> > InventoryId) VALUES (1, 1, 1, 1000, 1)
> > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
UnitPrice,
> > InventoryId) VALUES (2, 1, 1, 2000, 3)
> > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
UnitPrice,
> > InventoryId) VALUES (3, 1, 2, 4000, 6)

> > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (1, 1, 1, 0.1, 0)
> > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (2, 1, 2, 0.3, 0)
> > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (3, 1, 4, 0, 100)
> > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID, InvoiceItemID,
> > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (4, 2, 3, 0.3, 0)

> > The Problem:

> > I am trying to calculate for each line on an invoice the value after
> > applying discounts. The base calculation is InvoiceItems.Quantity *
> > InvoiceItems.UnitPriceEach. From that starting point, I have to apply
> > discounts in the DiscountTypes.Sequence Order - discount rate is either
> > calculated on the running balance (DiscountTypes.CalcOnBal = 1) or on
the
> > total value. Discount Amounts are also applied in the order of the
> > DiscountType.Sequence.

> of
> > 10% and 20% respectively (in that order), both calconbal = 1, then I
want
> to
> > end up with 3000 - 300 = 2700 - 540 = 2160.

> > Any help is greatly appreciated.

> > Alan Perkins

by **Itzik Ben-Ga** » Mon, 15 Apr 2002 19:44:23

Thanks Alan,

If DiscountTypes' PK is placed on DiscountTypeID, then the last row in your
script is a dup (second 2 DiscountTypeID). I'll assume it's supposed to be
4.

If all discount rates with CalcOnBal = 1 were grouped together (with all
other discounts grouped at the beginning and/or end of the sequence of
discounts), a set based query could have been written to solve the problem
by using logarithms to calculate an aggregate product of the grouped
discount rates. However, if rates with CalcOnBal = 1 and all other discounts
might be mingled, I can't think of an easy way to avoid using a cursor:

CREATE VIEW VInv
AS

SELECT
II.InvoiceID,
II.InvoiceItemID,
II.Quantity*II.UnitPrice AS Value,
ISNULL(IID.DiscountRate, 0) AS DiscountRate,
ISNULL(DiscountAmount, 0) AS DiscountAmount,
ISNULL(DT.CalcOnBal, 0) AS CalcOnBal,
ISNULL(DT.Sequence, 0) AS Sequence
FROM InvoiceItems AS II
LEFT OUTER JOIN InvoiceItemDiscounts AS IID
ON II.InvoiceItemID = IID.InvoiceItemID
LEFT OUTER JOIN DiscountTypes AS DT
ON IID.DiscountTypeID = DT.DiscountTypeID
GO

--

SET NOCOUNT ON

CREATE TABLE #Inv
(
InvoiceID INT NOT NULL,
InvoiceItemID INT NOT NULL,
Value MONEY NOT NULL,
PRIMARY KEY (InvoiceID, InvoiceItemID)
)

DECLARE
@InvoiceID AS INT, @PrevInvoiceID AS INT,
@InvoiceItemID AS INT, @PrevInvoiceItemID AS INT,
@Value AS MONEY, @RunningValue AS MONEY,
@DiscountRate AS FLOAT,
@DiscountAmount AS MONEY,
@CalcOnBal AS BIT,
@Sequence AS INT

SELECT @PrevInvoiceID = -1, @PrevInvoiceItemID = -1

DECLARE inv_cursor CURSOR FAST_FORWARD FOR
SELECT
InvoiceID,
InvoiceItemID,
Value,
DiscountRate,
DiscountAmount,
CalcOnBal
FROM VInv
ORDER BY InvoiceID, InvoiceItemID, Sequence

OPEN inv_cursor

FETCH NEXT FROM inv_cursor
INTO @InvoiceID, @InvoiceItemID, @Value, @DiscountRate,
@DiscountAmount, @CalcOnBal

WHILE @@FETCH_STATUS = 0
BEGIN

IF @InvoiceID <> @PrevInvoiceID OR @InvoiceItemID <> @PrevInvoiceItemID
BEGIN
IF @PrevInvoiceID <> - 1
INSERT INTO #Inv
VALUES(@PrevInvoiceID, @PrevInvoiceItemID, @RunningValue)

SET @RunningValue = @value
END

SET @RunningValue = @RunningValue -
CASE
WHEN @CalcOnBal = 1 THEN @RunningValue
ELSE @Value
END * @DiscountRate - @DiscountAmount

SELECT @PrevInvoiceID = @InvoiceID, @PrevInvoiceItemID = @InvoiceItemID

FETCH NEXT FROM inv_cursor
INTO @InvoiceID, @InvoiceItemID, @Value, @DiscountRate,
@DiscountAmount, @CalcOnBal

END

IF @PrevInvoiceID <> - 1
INSERT INTO #Inv
VALUES(@PrevInvoiceID, @PrevInvoiceItemID, @RunningValue)

CLOSE inv_cursor
DEALLOCATE inv_cursor

SELECT * FROM #Inv
ORDER BY InvoiceID, InvoiceItemID

DROP TABLE #Inv
GO

InvoiceID InvoiceItemID Value
----------- ------------- ---------------------
1 1 530.0000
1 2 1400.0000
1 3 8000.0000

--
BG

SQL Server MVP
Hi-Tech College, Israel
http://sql.hi-tech.co.il
http://www.tsqlsolutions.com
http://www.sqlmag.com

"Alan Perkins" <alan.perk...@protel.com.au> wrote in message

news:OZsVce44BHA.1752@tkmsftngp02...

> Keys:

> #InvoiceItems.InvoiceItemID:

> CONSTRAINT [aaaaaInvoiceItems_PK] PRIMARY KEY NONCLUSTERED
> (
> [InvoiceItemID]
> ) WITH FILLFACTOR = 70 ON [PRIMARY]

> #InvoiceItemDiscounts:

> CONSTRAINT [InvoiceItemDiscounts_PK] PRIMARY KEY CLUSTERED
> (
> [InvoiceItemDiscountID]
> ) ON [PRIMARY] ,
> CONSTRAINT [DiscountTypes_InvoiceItemDiscounts_FK1] FOREIGN KEY
> (
> [DiscountTypeID]
> ) REFERENCES [DiscountTypes] (
> [DiscountTypeID]
> ),
> CONSTRAINT [InvoiceItems_InvoiceItemDiscounts_FK1] FOREIGN KEY
> (
> [InvoiceItemID]
> ) REFERENCES [InvoiceItems] (
> [InvoiceItemID]
> ) ON DELETE CASCADE

> #DiscountTypes:

> CONSTRAINT [DiscountTypes_PK] PRIMARY KEY CLUSTERED
> (
> [DiscountTypeID]
> ) ON [PRIMARY]

> Thus InvoiceItems has a 1 to many relationship with InvoiceItemDiscounts
> (InvoiceItems INNER JOIN InvoiceItemDiscounts ON

InvoiceItems.InvoiceItemID

- Show quoted text -

> = InvoiceItemDiscounts.InvoiceItemID)

> InvoiceItemDiscounts has a 1 to many relationship with DiscountTypes
> InvoiceItemDiscounts.DiscountTypeID = DiscountTypes.DiscountTypeID

> The result I am after is the net value for each InvoiceItems.InvoiceItemID
> of (Quantity * UnitPrice) less any applicable discounts. From there I will
> apply the total to invoices as a whole, but that is beyond the scope of
the
> problem.

> Thanks again for any help.

> "Itzik Ben-Gan" <itz...@hi-tech.co.il> wrote in message
> news:#slnQW44BHA.492@tkmsftngp02...
> > Alan,

> > Thanks for the DDL and sample data.

> > Can you please also specify PKs and FKs, plus provide the desired
output?

> > Thanks,
> > --
> > BG

> > SQL Server MVP
> > Hi-Tech College, Israel
> > http://sql.hi-tech.co.il
> > http://www.tsqlsolutions.com
> > http://www.sqlmag.com

> > "Alan Perkins" <alan.perk...@protel.com.au> wrote in message
> > news:O0ay9J44BHA.984@tkmsftngp05...
> > > Simplified DDL:

> > > CREATE TABLE #InvoiceItems (
> > > InvoiceItemID int NOT NULL ,
> > > InvoiceID int NULL ,
> > > Quantity float NULL ,
> > > UnitPrice money NULL ,
> > > InventoryId int NULL
> > > )

> > > CREATE TABLE #InvoiceItemDiscounts (
> > > InvoiceItemDiscountID int NOT NULL ,
> > > InvoiceItemID int NOT NULL ,
> > > DiscountTypeID int NOT NULL ,
> > > DiscountRate float NOT NULL ,
> > > DiscountAmount money NOT NULL,
> > > )

> > > CREATE TABLE #DiscountTypes (
> > > DiscountTypeID int NOT NULL ,
> > > Description nvarchar(80),
> > > CalcOnBal bit NOT NULL ,
> > > Sequence int NULL
> > > )

> > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > Sequence) VALUES (1, 'Volume Discount', 1, 2)
> > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > Sequence) VALUES (2, 'Education Discount', 1, 1)
> > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > Sequence) VALUES (3, 'Reseller Discount', 0, 3)
> > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > Sequence) VALUES (2, 'Amount', 1, 4)

> > > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
> UnitPrice,
> > > InventoryId) VALUES (1, 1, 1, 1000, 1)
> > > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
> UnitPrice,
> > > InventoryId) VALUES (2, 1, 1, 2000, 3)
> > > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
> UnitPrice,
> > > InventoryId) VALUES (3, 1, 2, 4000, 6)

> > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
InvoiceItemID,
> > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (1, 1, 1, 0.1, 0)
> > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
InvoiceItemID,
> > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (2, 1, 2, 0.3, 0)
> > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
InvoiceItemID,
> > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (3, 1, 4, 0,
100)
> > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
InvoiceItemID,
> > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (4, 2, 3, 0.3, 0)

> > > The Problem:

> > > I am trying to calculate for each line on an invoice the value after
> > > applying discounts. The base calculation is InvoiceItems.Quantity *
> > > InvoiceItems.UnitPriceEach. From that starting point, I have to apply
> > > discounts in the DiscountTypes.Sequence Order - discount rate is
either
> > > calculated on the running balance (DiscountTypes.CalcOnBal = 1) or on
> the
> > > total value. Discount Amounts are also applied in the order of the
> > > DiscountType.Sequence.

> > > So If I have an invoiceline with 3 units @ 1000 each, with two
discounts
> > of
> > > 10% and 20% respectively (in that order), both calconbal = 1, then I
> want
> > to
> > > end up with 3000 - 300 = 2700 - 540 = 2160.

> > > Any help is greatly appreciated.

> > > Alan Perkins

by **Alan Perkin** » Mon, 15 Apr 2002 19:56:03

Thank you very, very much Itzik.

I figured I was going to have to resort to a cursor, which is unfortunate,
as I think speed might be a big issue.

Alan Perkins

"Itzik Ben-Gan" <itz...@hi-tech.co.il> wrote in message

news:e$$imG64BHA.2620@tkmsftngp04...

> Thanks Alan,

> If DiscountTypes' PK is placed on DiscountTypeID, then the last row in
your
> script is a dup (second 2 DiscountTypeID). I'll assume it's supposed to be
> 4.

> If all discount rates with CalcOnBal = 1 were grouped together (with all
> other discounts grouped at the beginning and/or end of the sequence of
> discounts), a set based query could have been written to solve the problem
> by using logarithms to calculate an aggregate product of the grouped
> discount rates. However, if rates with CalcOnBal = 1 and all other
discounts
> might be mingled, I can't think of an easy way to avoid using a cursor:

> CREATE VIEW VInv
> AS

> SELECT
> II.InvoiceID,
> II.InvoiceItemID,
> II.Quantity*II.UnitPrice AS Value,
> ISNULL(IID.DiscountRate, 0) AS DiscountRate,
> ISNULL(DiscountAmount, 0) AS DiscountAmount,
> ISNULL(DT.CalcOnBal, 0) AS CalcOnBal,
> ISNULL(DT.Sequence, 0) AS Sequence
> FROM InvoiceItems AS II
> LEFT OUTER JOIN InvoiceItemDiscounts AS IID
> ON II.InvoiceItemID = IID.InvoiceItemID
> LEFT OUTER JOIN DiscountTypes AS DT
> ON IID.DiscountTypeID = DT.DiscountTypeID
> GO

> --

> SET NOCOUNT ON

> CREATE TABLE #Inv
> (
> InvoiceID INT NOT NULL,
> InvoiceItemID INT NOT NULL,
> Value MONEY NOT NULL,
> PRIMARY KEY (InvoiceID, InvoiceItemID)
> )

> DECLARE
> @InvoiceID AS INT, @PrevInvoiceID AS INT,
> @InvoiceItemID AS INT, @PrevInvoiceItemID AS INT,
> @Value AS MONEY, @RunningValue AS MONEY,
> @DiscountRate AS FLOAT,
> @DiscountAmount AS MONEY,
> @CalcOnBal AS BIT,
> @Sequence AS INT

> SELECT @PrevInvoiceID = -1, @PrevInvoiceItemID = -1

> DECLARE inv_cursor CURSOR FAST_FORWARD FOR
> SELECT
> InvoiceID,
> InvoiceItemID,
> Value,
> DiscountRate,
> DiscountAmount,
> CalcOnBal
> FROM VInv
> ORDER BY InvoiceID, InvoiceItemID, Sequence

> OPEN inv_cursor

> FETCH NEXT FROM inv_cursor
> INTO @InvoiceID, @InvoiceItemID, @Value, @DiscountRate,
> @DiscountAmount, @CalcOnBal

> WHILE @@FETCH_STATUS = 0
> BEGIN

> IF @InvoiceID <> @PrevInvoiceID OR @InvoiceItemID <> @PrevInvoiceItemID
> BEGIN
> IF @PrevInvoiceID <> - 1
> INSERT INTO #Inv
> VALUES(@PrevInvoiceID, @PrevInvoiceItemID, @RunningValue)

> SET @RunningValue = @value
> END

> SET @RunningValue = @RunningValue -
> CASE
> WHEN @CalcOnBal = 1 THEN @RunningValue
> ELSE @Value
> END * @DiscountRate - @DiscountAmount

> SELECT @PrevInvoiceID = @InvoiceID, @PrevInvoiceItemID = @InvoiceItemID

> FETCH NEXT FROM inv_cursor
> INTO @InvoiceID, @InvoiceItemID, @Value, @DiscountRate,
> @DiscountAmount, @CalcOnBal

> END

> IF @PrevInvoiceID <> - 1
> INSERT INTO #Inv
> VALUES(@PrevInvoiceID, @PrevInvoiceItemID, @RunningValue)

> CLOSE inv_cursor
> DEALLOCATE inv_cursor

> SELECT * FROM #Inv
> ORDER BY InvoiceID, InvoiceItemID

> DROP TABLE #Inv
> GO

> InvoiceID InvoiceItemID Value
> ----------- ------------- ---------------------
> 1 1 530.0000
> 1 2 1400.0000
> 1 3 8000.0000

> --
> BG

> SQL Server MVP
> Hi-Tech College, Israel
> http://sql.hi-tech.co.il
> http://www.tsqlsolutions.com
> http://www.sqlmag.com

> "Alan Perkins" <alan.perk...@protel.com.au> wrote in message
> news:OZsVce44BHA.1752@tkmsftngp02...
> > Keys:

> > #InvoiceItems.InvoiceItemID:

> > CONSTRAINT [aaaaaInvoiceItems_PK] PRIMARY KEY NONCLUSTERED
> > (
> > [InvoiceItemID]
> > ) WITH FILLFACTOR = 70 ON [PRIMARY]

> > #InvoiceItemDiscounts:

> > CONSTRAINT [InvoiceItemDiscounts_PK] PRIMARY KEY CLUSTERED
> > (
> > [InvoiceItemDiscountID]
> > ) ON [PRIMARY] ,
> > CONSTRAINT [DiscountTypes_InvoiceItemDiscounts_FK1] FOREIGN KEY
> > (
> > [DiscountTypeID]
> > ) REFERENCES [DiscountTypes] (
> > [DiscountTypeID]
> > ),
> > CONSTRAINT [InvoiceItems_InvoiceItemDiscounts_FK1] FOREIGN KEY
> > (
> > [InvoiceItemID]
> > ) REFERENCES [InvoiceItems] (
> > [InvoiceItemID]
> > ) ON DELETE CASCADE

> > #DiscountTypes:

> > CONSTRAINT [DiscountTypes_PK] PRIMARY KEY CLUSTERED
> > (
> > [DiscountTypeID]
> > ) ON [PRIMARY]

> > Thus InvoiceItems has a 1 to many relationship with InvoiceItemDiscounts
> > (InvoiceItems INNER JOIN InvoiceItemDiscounts ON
> InvoiceItems.InvoiceItemID
> > = InvoiceItemDiscounts.InvoiceItemID)

> > InvoiceItemDiscounts has a 1 to many relationship with DiscountTypes
> > InvoiceItemDiscounts.DiscountTypeID = DiscountTypes.DiscountTypeID

> > The result I am after is the net value for each

InvoiceItems.InvoiceItemID

- Show quoted text -

> > of (Quantity * UnitPrice) less any applicable discounts. From there I
will
> > apply the total to invoices as a whole, but that is beyond the scope of
> the
> > problem.

> > Thanks again for any help.

> > "Itzik Ben-Gan" <itz...@hi-tech.co.il> wrote in message
> > news:#slnQW44BHA.492@tkmsftngp02...
> > > Alan,

> > > Thanks for the DDL and sample data.

> > > Can you please also specify PKs and FKs, plus provide the desired
> output?

> > > Thanks,
> > > --
> > > BG

> > > SQL Server MVP
> > > Hi-Tech College, Israel
> > > http://sql.hi-tech.co.il
> > > http://www.tsqlsolutions.com
> > > http://www.sqlmag.com

> > > "Alan Perkins" <alan.perk...@protel.com.au> wrote in message
> > > news:O0ay9J44BHA.984@tkmsftngp05...
> > > > Simplified DDL:

> > > > CREATE TABLE #InvoiceItems (
> > > > InvoiceItemID int NOT NULL ,
> > > > InvoiceID int NULL ,
> > > > Quantity float NULL ,
> > > > UnitPrice money NULL ,
> > > > InventoryId int NULL
> > > > )

> > > > CREATE TABLE #InvoiceItemDiscounts (
> > > > InvoiceItemDiscountID int NOT NULL ,
> > > > InvoiceItemID int NOT NULL ,
> > > > DiscountTypeID int NOT NULL ,
> > > > DiscountRate float NOT NULL ,
> > > > DiscountAmount money NOT NULL,
> > > > )

> > > > CREATE TABLE #DiscountTypes (
> > > > DiscountTypeID int NOT NULL ,
> > > > Description nvarchar(80),
> > > > CalcOnBal bit NOT NULL ,
> > > > Sequence int NULL
> > > > )

> > > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > > Sequence) VALUES (1, 'Volume Discount', 1, 2)
> > > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > > Sequence) VALUES (2, 'Education Discount', 1, 1)
> > > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > > Sequence) VALUES (3, 'Reseller Discount', 0, 3)
> > > > INSERT INTO #DiscountTypes (DiscountTypeID, Description, CalcOnBal,
> > > > Sequence) VALUES (2, 'Amount', 1, 4)

> > > > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
> > UnitPrice,
> > > > InventoryId) VALUES (1, 1, 1, 1000, 1)
> > > > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
> > UnitPrice,
> > > > InventoryId) VALUES (2, 1, 1, 2000, 3)
> > > > INSERT INTO #InvoiceItems (InvoiceItemID, InvoiceID, Quantity,
> > UnitPrice,
> > > > InventoryId) VALUES (3, 1, 2, 4000, 6)

> > > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
> InvoiceItemID,
> > > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (1, 1, 1, 0.1,
0)
> > > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
> InvoiceItemID,
> > > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (2, 1, 2, 0.3,
0)
> > > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
> InvoiceItemID,
> > > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (3, 1, 4, 0,
> 100)
> > > > INSERT INTO #InvoiceItemDiscounts (InvoiceItemDiscountID,
> InvoiceItemID,
> > > > DiscountTypeID, DiscountRate, DiscountAmount) VALUES (4, 2, 3, 0.3,
0)

> > > > The Problem:

> > > > I am trying to calculate for each line on an invoice the value after
> > > > applying discounts. The base calculation is InvoiceItems.Quantity *
> > > > InvoiceItems.UnitPriceEach. From that starting point, I have to
apply
> > > > discounts in the DiscountTypes.Sequence Order - discount rate is
> either
> > > > calculated on the running balance (DiscountTypes.CalcOnBal = 1) or
on
> > the
> > > > total value. Discount Amounts are also applied in the order of the
> > > > DiscountType.Sequence.

> > > > So If I have an invoiceline with 3 units @ 1000 each, with two
> discounts
> > > of
> > > > 10% and 20% respectively (in that order), both calconbal = 1, then I
> > want
> > > to
> > > > end up with 3000 - 300 = 2700 - 540 = 2160.

> > > > Any help is greatly appreciated.

> > > > Alan Perkins

by **Isaac Blan** » Wed, 17 Apr 2002 00:35:26

I doubt there is a set-based solution. On the other hand, my guess (and
your hope) would be #InvoiceItemDiscounts is populated one row at a time,
so you can pre-calculate all discount amounts at the insertion time if you
make sure rows are inserted in the order of DiscountTypes.sequence.

by **Joe Celk** » Wed, 17 Apr 2002 01:35:53

Am I missing something? The discounts are percentages, so the order of
multiplication does not matter.

((quantity * unit_price) * discount#1) * discount#2 =
((quantity * unit_price) * discount#2) * discount#1

What comes to mind is a monster discount expression, which you can load
from a table, if you wish

SUM(quantity * unit_price) *
((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
* CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
* ...) AS total_after_discounts

If they happen to be actual amounts, you can use

SUM(quantity * unit_price) -
((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
+ CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
+ ...) AS total_after_rebates

Obviously the rebates and discount expressions can be combined.

I have other questions about all those NULLs (moe than you usually see
in an entire database!), the use of FLOAT for quantity (silly and
dangerous) and financial calculations (illegal and dangerous), names
with absurd prefixes like "-type_id" (what is the difference between a
type and type-id in a data model? Type_id cannot exist in a data model,
since it is a meta-data concept), the use of the proprietary BIT
datatype, etc.

But for now, let's stick to algebra, then we'll get to SQL.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Alan Perkin** » Wed, 17 Apr 2002 12:32:04

Joe,

The issue I can't get around is when some discounts are to be calculated on
the total and others on the running balance.

Let's take a simple example

Quantity Q = 1
Price P = 1000
Discount1 Dc1 = 10% is calc'd on balance
Discount2 Dc2 = 20% is calc'd on balance
Discount3 Da3 = $100 is a rebate amount
Discount4 Dt4 = 10% is calc'd on total
Discount5 Dc5 = 10% is calc'd on balance

How would you express this expression in SQL as
((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)

without building it dynamically with a cursor? Forgive me, but I didn't
understand how to apply your example to this problem, but it sounds like
there is a solution there somewhere.

On the other issues you raised, for which I thank you, the nulls should have
been not null, I agree. The quantity as float is a mistake and I will change
that to a decimal.

The reason for the ID suffix is based on a convention that all tables have
identifiers (int identity) and they are named after the table in the
singular with ID appended; the table always being named in the plural, thus
Invoices.InvoiceID, DiscountTypes.DiscountID, Transactors.TransactorID. I
know it is conceptually not strictly correct, but having come into a
database culture where any name goes, and it is impossible to find the name
of a column because of many different variations, I found it necessary to
enforce one rule for all. Is that so bad?

Quote:> Am I missing something? The discounts are percentages, so the order of
> multiplication does not matter.

> ((quantity * unit_price) * discount#1) * discount#2 =
> ((quantity * unit_price) * discount#2) * discount#1

> What comes to mind is a monster discount expression, which you can load
> from a table, if you wish

> SUM(quantity * unit_price) *
> ((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
> * CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
> * ...) AS total_after_discounts

> If they happen to be actual amounts, you can use

> SUM(quantity * unit_price) -
> ((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
> + CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
> + ...) AS total_after_rebates

> Obviously the rebates and discount expressions can be combined.

> I have other questions about all those NULLs (moe than you usually see
> in an entire database!), the use of FLOAT for quantity (silly and
> dangerous) and financial calculations (illegal and dangerous), names
> with absurd prefixes like "-type_id" (what is the difference between a
> type and type-id in a data model? Type_id cannot exist in a data model,
> since it is a meta-data concept), the use of the proprietary BIT
> datatype, etc.

> But for now, let's stick to algebra, then we'll get to SQL.

> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

by **Steve Kas** » Wed, 17 Apr 2002 13:08:53

Alan,

I understand the difference between a rebate ($10 off) and a discount (10% off),
but what is going on with Discount4? You are subtracting .90*Q*P in
your example, which doesn't make sense to me.

Except for that, I think you have a chance of a set-based solution, since the true
value of any rebates is discounted by the discounts with larger sequence numbers.
You would then have something manageable - the ticket price discounted by all
percent-off discounts, minus the sum of rebates, where each rebate is discounted
by all percent-off discounts that are applied after the rebate.

select Q*P*PRODUCT(1- percent_discount) --over all discounts
- SUM(rebate*PRODUCT(1- percent_discount))
--where percent_discount has higher sequence number than rebate

Steve Kass
Drew University

> Joe,

> The issue I can't get around is when some discounts are to be calculated on
> the total and others on the running balance.

> Let's take a simple example

> Quantity Q = 1
> Price P = 1000
> Discount1 Dc1 = 10% is calc'd on balance
> Discount2 Dc2 = 20% is calc'd on balance
> Discount3 Da3 = $100 is a rebate amount
> Discount4 Dt4 = 10% is calc'd on total
> Discount5 Dc5 = 10% is calc'd on balance

> How would you express this expression in SQL as
> ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)

> without building it dynamically with a cursor? Forgive me, but I didn't
> understand how to apply your example to this problem, but it sounds like
> there is a solution there somewhere.

> On the other issues you raised, for which I thank you, the nulls should have
> been not null, I agree. The quantity as float is a mistake and I will change
> that to a decimal.

> The reason for the ID suffix is based on a convention that all tables have
> identifiers (int identity) and they are named after the table in the
> singular with ID appended; the table always being named in the plural, thus
> Invoices.InvoiceID, DiscountTypes.DiscountID, Transactors.TransactorID. I
> know it is conceptually not strictly correct, but having come into a
> database culture where any name goes, and it is impossible to find the name
> of a column because of many different variations, I found it necessary to
> enforce one rule for all. Is that so bad?

> > Am I missing something? The discounts are percentages, so the order of
> > multiplication does not matter.

> > ((quantity * unit_price) * discount#1) * discount#2 =
> > ((quantity * unit_price) * discount#2) * discount#1

> > What comes to mind is a monster discount expression, which you can load
> > from a table, if you wish

> > SUM(quantity * unit_price) *
> > ((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
> > * CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
> > * ...) AS total_after_discounts

> > If they happen to be actual amounts, you can use

> > SUM(quantity * unit_price) -
> > ((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
> > + CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
> > + ...) AS total_after_rebates

> > Obviously the rebates and discount expressions can be combined.

> > I have other questions about all those NULLs (moe than you usually see
> > in an entire database!), the use of FLOAT for quantity (silly and
> > dangerous) and financial calculations (illegal and dangerous), names
> > with absurd prefixes like "-type_id" (what is the difference between a
> > type and type-id in a data model? Type_id cannot exist in a data model,
> > since it is a meta-data concept), the use of the proprietary BIT
> > datatype, etc.

> > But for now, let's stick to algebra, then we'll get to SQL.

> > --CELKO--
> > ===========================
> > Please post DDL, so that people do not have to guess what the keys,
> > constraints, Declarative Referential Integrity, datatypes, etc. in your
> > schema are.

> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!

by **Alan Perkin** » Wed, 17 Apr 2002 13:41:37

D'Oh!
((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)
should be
((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(Dt4))) * (1-Dc5)

So, to further your example, it is possible to construct:

select Q*P*PRODUCT(1- percent_discount) --over all discounts where calc on
balance
LESS SUM(rebate*PRODUCT(1- percent_discount)) -- where discount has
higher sequence number than rebate
LESS SUM(Q*P*percent_discount) -- where discount is not calc on balance
LESS SUM(rebate * PRODUCT( percent_discount)) -- where discount is not
calc on balance

This I can do!

Thanks Steve, Thanks Joe, Thanks Itzik

> Alan,

> I understand the difference between a rebate ($10 off) and a discount
(10% off),
> but what is going on with Discount4? You are subtracting .90*Q*P in
> your example, which doesn't make sense to me.

> Except for that, I think you have a chance of a set-based solution,
since the true
> value of any rebates is discounted by the discounts with larger sequence
numbers.
> You would then have something manageable - the ticket price discounted by
all
> percent-off discounts, minus the sum of rebates, where each rebate is
discounted
> by all percent-off discounts that are applied after the rebate.

> select Q*P*PRODUCT(1- percent_discount) --over all discounts
> - SUM(rebate*PRODUCT(1- percent_discount))
> --where percent_discount has higher sequence number than rebate

> Steve Kass
> Drew University

> > Joe,

> > The issue I can't get around is when some discounts are to be calculated
on
> > the total and others on the running balance.

> > Let's take a simple example

> > Quantity Q = 1
> > Price P = 1000
> > Discount1 Dc1 = 10% is calc'd on balance
> > Discount2 Dc2 = 20% is calc'd on balance
> > Discount3 Da3 = $100 is a rebate amount
> > Discount4 Dt4 = 10% is calc'd on total
> > Discount5 Dc5 = 10% is calc'd on balance

> > How would you express this expression in SQL as
> > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)

> > without building it dynamically with a cursor? Forgive me, but I didn't
> > understand how to apply your example to this problem, but it sounds like
> > there is a solution there somewhere.

> > On the other issues you raised, for which I thank you, the nulls should
have
> > been not null, I agree. The quantity as float is a mistake and I will
change
> > that to a decimal.

> > The reason for the ID suffix is based on a convention that all tables
have
> > identifiers (int identity) and they are named after the table in the
> > singular with ID appended; the table always being named in the plural,
thus
> > Invoices.InvoiceID, DiscountTypes.DiscountID, Transactors.TransactorID.
I
> > know it is conceptually not strictly correct, but having come into a
> > database culture where any name goes, and it is impossible to find the
name
> > of a column because of many different variations, I found it necessary
to
> > enforce one rule for all. Is that so bad?

> > > Am I missing something? The discounts are percentages, so the order
of
> > > multiplication does not matter.

> > > ((quantity * unit_price) * discount#1) * discount#2 =
> > > ((quantity * unit_price) * discount#2) * discount#1

> > > What comes to mind is a monster discount expression, which you can
load
> > > from a table, if you wish

> > > SUM(quantity * unit_price) *
> > > ((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
> > > * CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
> > > * ...) AS total_after_discounts

> > > If they happen to be actual amounts, you can use

> > > SUM(quantity * unit_price) -
> > > ((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
> > > + CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
> > > + ...) AS total_after_rebates

> > > Obviously the rebates and discount expressions can be combined.

> > > I have other questions about all those NULLs (moe than you usually see
> > > in an entire database!), the use of FLOAT for quantity (silly and
> > > dangerous) and financial calculations (illegal and dangerous), names
> > > with absurd prefixes like "-type_id" (what is the difference between a
> > > type and type-id in a data model? Type_id cannot exist in a data
model,
> > > since it is a meta-data concept), the use of the proprietary BIT
> > > datatype, etc.

> > > But for now, let's stick to algebra, then we'll get to SQL.

> > > --CELKO--
> > > ===========================
> > > Please post DDL, so that people do not have to guess what the keys,
> > > constraints, Declarative Referential Integrity, datatypes, etc. in
your
> > > schema are.

> > > *** Sent via Developersdex http://www.developersdex.com ***
> > > Don't just participate in USENET...get rewarded for it!

by **Steve Kas** » Wed, 17 Apr 2002 13:58:02

Alan,

Just be careful with the discounts that are not calc on balance. Since
they have a sequence number, the position matters - discounts with
a higher sequence number diminish their value, but discounts with
a lower sequence number do not. You can probably change them
to equivalent calc-on-balance discounts of (1-Dt4)/product(1-Dc?)
where the product is over previous sequence numbers of calc-on-balance
discounts.

Steve

> D'Oh!
> ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)
> should be
> ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(Dt4))) * (1-Dc5)

> So, to further your example, it is possible to construct:

> select Q*P*PRODUCT(1- percent_discount) --over all discounts where calc on
> balance
> LESS SUM(rebate*PRODUCT(1- percent_discount)) -- where discount has
> higher sequence number than rebate
> LESS SUM(Q*P*percent_discount) -- where discount is not calc on balance
> LESS SUM(rebate * PRODUCT( percent_discount)) -- where discount is not
> calc on balance

> This I can do!

> Thanks Steve, Thanks Joe, Thanks Itzik

> > Alan,

> > I understand the difference between a rebate ($10 off) and a discount
> (10% off),
> > but what is going on with Discount4? You are subtracting .90*Q*P in
> > your example, which doesn't make sense to me.

> > Except for that, I think you have a chance of a set-based solution,
> since the true
> > value of any rebates is discounted by the discounts with larger sequence
> numbers.
> > You would then have something manageable - the ticket price discounted by
> all
> > percent-off discounts, minus the sum of rebates, where each rebate is
> discounted
> > by all percent-off discounts that are applied after the rebate.

> > select Q*P*PRODUCT(1- percent_discount) --over all discounts
> > - SUM(rebate*PRODUCT(1- percent_discount))
> > --where percent_discount has higher sequence number than rebate

> > Steve Kass
> > Drew University

> > > Joe,

> > > The issue I can't get around is when some discounts are to be calculated
> on
> > > the total and others on the running balance.

> > > Let's take a simple example

> > > Quantity Q = 1
> > > Price P = 1000
> > > Discount1 Dc1 = 10% is calc'd on balance
> > > Discount2 Dc2 = 20% is calc'd on balance
> > > Discount3 Da3 = $100 is a rebate amount
> > > Discount4 Dt4 = 10% is calc'd on total
> > > Discount5 Dc5 = 10% is calc'd on balance

> > > How would you express this expression in SQL as
> > > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)

> > > without building it dynamically with a cursor? Forgive me, but I didn't
> > > understand how to apply your example to this problem, but it sounds like
> > > there is a solution there somewhere.

> > > On the other issues you raised, for which I thank you, the nulls should
> have
> > > been not null, I agree. The quantity as float is a mistake and I will
> change
> > > that to a decimal.

> > > The reason for the ID suffix is based on a convention that all tables
> have
> > > identifiers (int identity) and they are named after the table in the
> > > singular with ID appended; the table always being named in the plural,
> thus
> > > Invoices.InvoiceID, DiscountTypes.DiscountID, Transactors.TransactorID.
> I
> > > know it is conceptually not strictly correct, but having come into a
> > > database culture where any name goes, and it is impossible to find the
> name
> > > of a column because of many different variations, I found it necessary
> to
> > > enforce one rule for all. Is that so bad?

> > > > Am I missing something? The discounts are percentages, so the order
> of
> > > > multiplication does not matter.

> > > > ((quantity * unit_price) * discount#1) * discount#2 =
> > > > ((quantity * unit_price) * discount#2) * discount#1

> > > > What comes to mind is a monster discount expression, which you can
> load
> > > > from a table, if you wish

> > > > SUM(quantity * unit_price) *
> > > > ((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
> > > > * CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
> > > > * ...) AS total_after_discounts

> > > > If they happen to be actual amounts, you can use

> > > > SUM(quantity * unit_price) -
> > > > ((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
> > > > + CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
> > > > + ...) AS total_after_rebates

> > > > Obviously the rebates and discount expressions can be combined.

> > > > I have other questions about all those NULLs (moe than you usually see
> > > > in an entire database!), the use of FLOAT for quantity (silly and
> > > > dangerous) and financial calculations (illegal and dangerous), names
> > > > with absurd prefixes like "-type_id" (what is the difference between a
> > > > type and type-id in a data model? Type_id cannot exist in a data
> model,
> > > > since it is a meta-data concept), the use of the proprietary BIT
> > > > datatype, etc.

> > > > But for now, let's stick to algebra, then we'll get to SQL.

> > > > --CELKO--
> > > > ===========================
> > > > Please post DDL, so that people do not have to guess what the keys,
> > > > constraints, Declarative Referential Integrity, datatypes, etc. in
> your
> > > > schema are.

> > > > *** Sent via Developersdex http://www.developersdex.com ***
> > > > Don't just participate in USENET...get rewarded for it!

by **Isaac Blan** » Thu, 18 Apr 2002 00:59:03

That's gonna be a formula from hell on top of a self-join (or a correlated
subqueryand) and group by. So a cursor-based solution will be orders of
magnitude simpler to understand and maintain - and probably as much slower.
If that is not a classical performance hit dilemma, then nothing is.

> Alan,

> Just be careful with the discounts that are not calc on balance. Since
> they have a sequence number, the position matters - discounts with
> a higher sequence number diminish their value, but discounts with
> a lower sequence number do not. You can probably change them
> to equivalent calc-on-balance discounts of (1-Dt4)/product(1-Dc?)
> where the product is over previous sequence numbers of calc-on-balance
> discounts.

> Steve

> > D'Oh!
> > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)
> > should be
> > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(Dt4))) * (1-Dc5)

> > So, to further your example, it is possible to construct:

> > select Q*P*PRODUCT(1- percent_discount) --over all discounts where calc
on
> > balance
> > LESS SUM(rebate*PRODUCT(1- percent_discount)) -- where discount has
> > higher sequence number than rebate
> > LESS SUM(Q*P*percent_discount) -- where discount is not calc on
balance
> > LESS SUM(rebate * PRODUCT( percent_discount)) -- where discount is
not
> > calc on balance

> > This I can do!

> > Thanks Steve, Thanks Joe, Thanks Itzik

> > > Alan,

> > > I understand the difference between a rebate ($10 off) and a
discount
> > (10% off),
> > > but what is going on with Discount4? You are subtracting .90*Q*P in
> > > your example, which doesn't make sense to me.

> > > Except for that, I think you have a chance of a set-based solution,
> > since the true
> > > value of any rebates is discounted by the discounts with larger
sequence
> > numbers.
> > > You would then have something manageable - the ticket price discounted
by
> > all
> > > percent-off discounts, minus the sum of rebates, where each rebate is
> > discounted
> > > by all percent-off discounts that are applied after the rebate.

> > > select Q*P*PRODUCT(1- percent_discount) --over all discounts
> > > - SUM(rebate*PRODUCT(1- percent_discount))
> > > --where percent_discount has higher sequence number than rebate

> > > Steve Kass
> > > Drew University

> > > > Joe,

> > > > The issue I can't get around is when some discounts are to be
calculated
> > on
> > > > the total and others on the running balance.

> > > > Let's take a simple example

> > > > Quantity Q = 1
> > > > Price P = 1000
> > > > Discount1 Dc1 = 10% is calc'd on balance
> > > > Discount2 Dc2 = 20% is calc'd on balance
> > > > Discount3 Da3 = $100 is a rebate amount
> > > > Discount4 Dt4 = 10% is calc'd on total
> > > > Discount5 Dc5 = 10% is calc'd on balance

> > > > How would you express this expression in SQL as
> > > > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)

> > > > without building it dynamically with a cursor? Forgive me, but I
didn't
> > > > understand how to apply your example to this problem, but it sounds
like
> > > > there is a solution there somewhere.

> > > > On the other issues you raised, for which I thank you, the nulls
should
> > have
> > > > been not null, I agree. The quantity as float is a mistake and I
will
> > change
> > > > that to a decimal.

> > > > The reason for the ID suffix is based on a convention that all
tables
> > have
> > > > identifiers (int identity) and they are named after the table in the
> > > > singular with ID appended; the table always being named in the
plural,
> > thus
> > > > Invoices.InvoiceID, DiscountTypes.DiscountID,

Transactors.TransactorID.

- Show quoted text -

> > I
> > > > know it is conceptually not strictly correct, but having come into a
> > > > database culture where any name goes, and it is impossible to find
the
> > name
> > > > of a column because of many different variations, I found it
necessary
> > to
> > > > enforce one rule for all. Is that so bad?

> > > > > Am I missing something? The discounts are percentages, so the
order
> > of
> > > > > multiplication does not matter.

> > > > > ((quantity * unit_price) * discount#1) * discount#2 =
> > > > > ((quantity * unit_price) * discount#2) * discount#1

> > > > > What comes to mind is a monster discount expression, which you can
> > load
> > > > > from a table, if you wish

> > > > > SUM(quantity * unit_price) *
> > > > > ((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
> > > > > * CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
> > > > > * ...) AS total_after_discounts

> > > > > If they happen to be actual amounts, you can use

> > > > > SUM(quantity * unit_price) -
> > > > > ((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
> > > > > + CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
> > > > > + ...) AS total_after_rebates

> > > > > Obviously the rebates and discount expressions can be combined.

> > > > > I have other questions about all those NULLs (moe than you usually
see
> > > > > in an entire database!), the use of FLOAT for quantity (silly and
> > > > > dangerous) and financial calculations (illegal and dangerous),
names
> > > > > with absurd prefixes like "-type_id" (what is the difference
between a
> > > > > type and type-id in a data model? Type_id cannot exist in a data
> > model,
> > > > > since it is a meta-data concept), the use of the proprietary BIT
> > > > > datatype, etc.

> > > > > But for now, let's stick to algebra, then we'll get to SQL.

> > > > > --CELKO--
> > > > > ===========================
> > > > > Please post DDL, so that people do not have to guess what the
keys,
> > > > > constraints, Declarative Referential Integrity, datatypes, etc. in
> > your
> > > > > schema are.

> > > > > *** Sent via Developersdex http://www.developersdex.com ***
> > > > > Don't just participate in USENET...get rewarded for it!

by **Steve Kas** » Thu, 18 Apr 2002 13:57:15

I think you're right - if it all works out, it might be fun to read, but not fun
in production.

Steve

> That's gonna be a formula from hell on top of a self-join (or a correlated
> subqueryand) and group by. So a cursor-based solution will be orders of
> magnitude simpler to understand and maintain - and probably as much slower.
> If that is not a classical performance hit dilemma, then nothing is.

> > Alan,

> > Just be careful with the discounts that are not calc on balance. Since
> > they have a sequence number, the position matters - discounts with
> > a higher sequence number diminish their value, but discounts with
> > a lower sequence number do not. You can probably change them
> > to equivalent calc-on-balance discounts of (1-Dt4)/product(1-Dc?)
> > where the product is over previous sequence numbers of calc-on-balance
> > discounts.

> > Steve

> > > D'Oh!
> > > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)
> > > should be
> > > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(Dt4))) * (1-Dc5)

> > > So, to further your example, it is possible to construct:

> > > select Q*P*PRODUCT(1- percent_discount) --over all discounts where calc
> on
> > > balance
> > > LESS SUM(rebate*PRODUCT(1- percent_discount)) -- where discount has
> > > higher sequence number than rebate
> > > LESS SUM(Q*P*percent_discount) -- where discount is not calc on
> balance
> > > LESS SUM(rebate * PRODUCT( percent_discount)) -- where discount is
> not
> > > calc on balance

> > > This I can do!

> > > Thanks Steve, Thanks Joe, Thanks Itzik

> > > > Alan,

> > > > I understand the difference between a rebate ($10 off) and a
> discount
> > > (10% off),
> > > > but what is going on with Discount4? You are subtracting .90*Q*P in
> > > > your example, which doesn't make sense to me.

> > > > Except for that, I think you have a chance of a set-based solution,
> > > since the true
> > > > value of any rebates is discounted by the discounts with larger
> sequence
> > > numbers.
> > > > You would then have something manageable - the ticket price discounted
> by
> > > all
> > > > percent-off discounts, minus the sum of rebates, where each rebate is
> > > discounted
> > > > by all percent-off discounts that are applied after the rebate.

> > > > select Q*P*PRODUCT(1- percent_discount) --over all discounts
> > > > - SUM(rebate*PRODUCT(1- percent_discount))
> > > > --where percent_discount has higher sequence number than rebate

> > > > Steve Kass
> > > > Drew University

> > > > > Joe,

> > > > > The issue I can't get around is when some discounts are to be
> calculated
> > > on
> > > > > the total and others on the running balance.

> > > > > Let's take a simple example

> > > > > Quantity Q = 1
> > > > > Price P = 1000
> > > > > Discount1 Dc1 = 10% is calc'd on balance
> > > > > Discount2 Dc2 = 20% is calc'd on balance
> > > > > Discount3 Da3 = $100 is a rebate amount
> > > > > Discount4 Dt4 = 10% is calc'd on total
> > > > > Discount5 Dc5 = 10% is calc'd on balance

> > > > > How would you express this expression in SQL as
> > > > > ((((Q*P)*(1-Dc1))*(1-Dc2)) - Da3 - ((Q*P)*(1-Dt4))) * (1-Dc5)

> > > > > without building it dynamically with a cursor? Forgive me, but I
> didn't
> > > > > understand how to apply your example to this problem, but it sounds
> like
> > > > > there is a solution there somewhere.

> > > > > On the other issues you raised, for which I thank you, the nulls
> should
> > > have
> > > > > been not null, I agree. The quantity as float is a mistake and I
> will
> > > change
> > > > > that to a decimal.

> > > > > The reason for the ID suffix is based on a convention that all
> tables
> > > have
> > > > > identifiers (int identity) and they are named after the table in the
> > > > > singular with ID appended; the table always being named in the
> plural,
> > > thus
> > > > > Invoices.InvoiceID, DiscountTypes.DiscountID,
> Transactors.TransactorID.
> > > I
> > > > > know it is conceptually not strictly correct, but having come into a
> > > > > database culture where any name goes, and it is impossible to find
> the
> > > name
> > > > > of a column because of many different variations, I found it
> necessary
> > > to
> > > > > enforce one rule for all. Is that so bad?

> > > > > > Am I missing something? The discounts are percentages, so the
> order
> > > of
> > > > > > multiplication does not matter.

> > > > > > ((quantity * unit_price) * discount#1) * discount#2 =
> > > > > > ((quantity * unit_price) * discount#2) * discount#1

> > > > > > What comes to mind is a monster discount expression, which you can
> > > load
> > > > > > from a table, if you wish

> > > > > > SUM(quantity * unit_price) *
> > > > > > ((CASE WHEN discount_1 = 'y' THEN 0.90000 ELSE 1.00 END
> > > > > > * CASE WHEN discount_2 = 'y' THEN 0.88000 ELSE 1.00 END
> > > > > > * ...) AS total_after_discounts

> > > > > > If they happen to be actual amounts, you can use

> > > > > > SUM(quantity * unit_price) -
> > > > > > ((CASE WHEN discount_3 = 'y' THEN 10.00 ELSE 0.00 END
> > > > > > + CASE WHEN discount_4 = 'y' THEN 2.98 ELSE 0.00 END
> > > > > > + ...) AS total_after_rebates

> > > > > > Obviously the rebates and discount expressions can be combined.

> > > > > > I have other questions about all those NULLs (moe than you usually
> see
> > > > > > in an entire database!), the use of FLOAT for quantity (silly and
> > > > > > dangerous) and financial calculations (illegal and dangerous),
> names
> > > > > > with absurd prefixes like "-type_id" (what is the difference
> between a
> > > > > > type and type-id in a data model? Type_id cannot exist in a data
> > > model,
> > > > > > since it is a meta-data concept), the use of the proprietary BIT
> > > > > > datatype, etc.

> > > > > > But for now, let's stick to algebra, then we'll get to SQL.

> > > > > > --CELKO--
> > > > > > ===========================
> > > > > > Please post DDL, so that people do not have to guess what the
> keys,
> > > > > > constraints, Declarative Referential Integrity, datatypes, etc. in
> > > your
> > > > > > schema are.

> > > > > > *** Sent via Developersdex http://www.developersdex.com ***
> > > > > > Don't just participate in USENET...get rewarded for it!

by **Joe Celk** » Thu, 18 Apr 2002 23:55:40

I'm slow this week; I did not get the part about applying things against
the totals. Duh.

Quote:>> I think you're right - if it all works out, it might be fun to read,

but not fun in production <<

But it is a great puzzle! I wonder if you could hide this in a user
function that grabs the discounts and rebates from a table then does a
procedural sequence of operations? That would be easier to maintain,
even if it lacks the religious purity we all seek.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Isaac Blan** » Fri, 19 Apr 2002 00:25:55

Quote:> But it is a great puzzle! I wonder if you could hide this in a user
> function that grabs the discounts and rebates from a table then does a
> procedural sequence of operations? That would be easier to maintain,
> even if it lacks the religious purity we all seek.

Tha's basically what I proposed earlier- pre-calculating actual discount
amounts sequentially when populating the discount table and storing them in
the approriate row. Then, whenever you get to a particular item, just grab
the sum af all discounts.

Very Computer

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order

SQL Puzzle - Applying calculations to running balances in a specified order