Tiered pricing calculation

We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation.  What we need to do is break up the total quantity passed to the function and return the total of prices found.  In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.

Low Qty       High Qty      Fee         Actual Qty        Price

0                  7500        0.025            7500           187.50

7501           15000        0.020            2221             44.42

Below is the table definition that we have to work with (ugghh).

CREATE TABLE [dbo].[BillingPriceTable](
	[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
	[entity] [varchar](4) NULL,
	[PriceTableCode] [varchar](10) NULL,
	[PriceTableName] [varchar](40) NULL,
	[PriceMethod] [varchar](5) NULL,
	[bd01] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd01]  DEFAULT ((0)),
	[bd02] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd02]  DEFAULT ((0)),
	[bd03] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd03]  DEFAULT ((0)),
	[bd04] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd04]  DEFAULT ((0)),
	[bd05] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd05]  DEFAULT ((0)),
	[bd06] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd06]  DEFAULT ((0)),
	[bd07] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd07]  DEFAULT ((0)),
	[bd08] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd08]  DEFAULT ((0)),
	[bd09] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd09]  DEFAULT ((0)),
	[bd10] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd10]  DEFAULT ((0)),
	[pr01] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr01]  DEFAULT ((0)),
	[pr02] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr02]  DEFAULT ((0)),
	[pr03] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr03]  DEFAULT ((0)),
	[pr04] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr04]  DEFAULT ((0)),
	[pr05] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr05]  DEFAULT ((0)),
	[pr06] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr06]  DEFAULT ((0)),
	[pr07] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr07]  DEFAULT ((0)),
	[pr08] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr08]  DEFAULT ((0)),
	[pr09] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr09]  DEFAULT ((0)),
	[pr10] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr10]  DEFAULT ((0)),
	[pr11] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr11]  DEFAULT ((0)),
	[modby] [varchar](12) NULL,
	[moddte] [datetime] NULL,
 CONSTRAINT [PK_BillingPriceTable] PRIMARY KEY CLUSTERED 
(
	[PriceTableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

What we have so far is shown below.  The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range.  So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628

The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.  Any help is appreciated.

ALTER FUNCTION [dbo].[fn_GetPrice] 
(
	@plincd		varchar(3),
	@pgrpcode	varchar(4),
	@pitmcode	varchar(4),
	@qty		decimal(10,1) = 1,
	@corpnbr	varchar(9)
)
RETURNS decimal(11,5)
AS 
	BEGIN 

		DECLARE @Price decimal(11,5);

		SELECT @Price = C.unitprc
		  FROM dbo.BillingPackages AS P INNER JOIN
			   dbo.BillingItemClassOfBilling AS C ON P.clsofbil = C.pcobcd AND P.plincd = C.plincd
		 WHERE (P.corpnbr = @corpnbr) 
		   AND (C.pgrpcd = @pgrpcode) 
		   AND (C.pitmcd = @pitmcode);

		IF @Price IS NULL
			BEGIN
				SELECT @Price = CASE WHEN I.prctable IS NULL THEN I.unitprc
									 ELSE (SELECT CASE WHEN @qty BETWEEN 0 AND bd01 THEN pr01 
													   WHEN (@qty >= bd01 AND bd02 = 0) OR (@qty BETWEEN bd01 AND bd02 - .01) THEN pr02
													   WHEN (@qty >= bd02 AND bd03 = 0) OR (@qty BETWEEN bd02 AND bd03 - .01) THEN pr03
													   WHEN (@qty >= bd03 AND bd04 = 0) OR (@qty BETWEEN bd03 AND bd04 - .01) THEN pr04
													   WHEN (@qty >= bd04 AND bd05 = 0) OR (@qty BETWEEN bd04 AND bd05 - .01) THEN pr05
													   WHEN (@qty >= bd05 AND bd06 = 0) OR (@qty BETWEEN bd05 AND bd06 - .01) THEN pr06
													   WHEN (@qty >= bd06 AND bd07 = 0) OR (@qty BETWEEN bd06 AND bd07 - .01) THEN pr07
													   WHEN (@qty >= bd07 AND bd08 = 0) OR (@qty BETWEEN bd07 AND bd08 - .01) THEN pr08
													   WHEN (@qty >= bd08 AND bd09 = 0) OR (@qty BETWEEN bd08 AND bd09 - .01) THEN pr09
													   WHEN (@qty >= bd09 AND bd10 = 0) OR (@qty BETWEEN bd09 AND bd10 - .01) THEN pr10
													   WHEN (@qty >= bd10) THEN pr11
													   ELSE 0
													   END
											 FROM dbo.BillingPriceTable 
											 WHERE PriceTableCode = I.prctable)
									 END
				  FROM dbo.BillingItemCodes AS I
				 WHERE (I.plincd = @plincd)
				   AND (I.pgrpcd = @pgrpcode)
				   AND (I.pitmcd = @pitmcode);

			END

		RETURN @Price;
	
	END 

April 30th, 2015 10:40am

Try

Insert dbo.BillingPriceTable(bd01, bd02, pr01, pr02) Values(7500, 15000, .025, .020);
go
Declare @PriceTableID int = 1;
Declare @Qty int = 9721;
Select Case When @Qty < b.bd01 Then @Qty Else b.bd01 End * b.pr01 + 
   Case When @Qty > b.bd01 Then Case When @Qty < b.bd02 Then @Qty - b.bd01 Else b.bd02 - b.bd01 End Else b.bd02 End * b.pr02 +
   Case When @Qty > b.bd02 Then Case When @Qty < b.bd03 Then @Qty - b.bd02 Else b.bd03 - b.bd02 End Else b.bd03 End * b.pr03 +
   Case When @Qty > b.bd03 Then Case When @Qty < b.bd04 Then @Qty - b.bd03 Else b.bd04 - b.bd03 End Else b.bd04 End * b.pr04 +
   Case When @Qty > b.bd04 Then Case When @Qty < b.bd05 Then @Qty - b.bd04 Else b.bd05 - b.bd04 End Else b.bd05 End * b.pr05 +
   Case When @Qty > b.bd05 Then Case When @Qty < b.bd06 Then @Qty - b.bd05 Else b.bd06 - b.bd05 End Else b.bd06 End * b.pr06 +
   Case When @Qty > b.bd06 Then Case When @Qty < b.bd07 Then @Qty - b.bd06 Else b.bd07 - b.bd06 End Else b.bd07 End * b.pr07 +
   Case When @Qty > b.bd07 Then Case When @Qty < b.bd08 Then @Qty - b.bd07 Else b.bd08 - b.bd07 End Else b.bd08 End * b.pr08 +
   Case When @Qty > b.bd08 Then Case When @Qty < b.bd09 Then @Qty - b.bd08 Else b.bd09 - b.bd08 End Else b.bd09 End * b.pr09 +
   Case When @Qty > b.bd09 Then Case When @Qty < b.bd10 Then @Qty - b.bd09 Else b.bd10 - b.bd09 End Else b.bd10 End * b.pr10 +
   Case When @Qty > b.bd10 Then @Qty - b.bd10 Else 0 End * b.pr11
From dbo.BillingPriceTable b

Note that the above code makes the assumption that the data you have in the table is correct.  That is, in each row, each successive bdxx is either 0 or greater than the previous bdxx (that is, for example, bd05 is either equal to 0 or it is greater than bd04).  Also, in each row, if a bdxx is equal to 0, all of the following bdxx's are equal to zero.  That is, for example, if bd05 is equal to zero, then bd06, 07, 08, 09, and 10 are equal to zero.

Tom

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 11:47am

That did not work.  When I used @Qty of 7490 it should have returned 187.25 (7490 * 0.025) but it returned 2437.25 and when I used @Qty of 9721 it should have returned 231.92 ((7500 * 0.025)+(2221 * 0.020)) but it returned 2181.92.  It needs to stop calculating when the @Qty falls in between the Low and High range.  Hope this helps.
April 30th, 2015 12:16pm

Yes, the previous code was incorrect.  Try the following (I created my own temporary table named ##BillingPriceTale so I could load data into it without affecting your real table and a #Quantities table so I could test multiple quantities with one query).  If you cut and paste this into a query window and run it, it produces the result you want.

CREATE TABLE #BillingPriceTable(
	[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
	[entity] [varchar](4) NULL,
	[PriceTableCode] [varchar](10) NULL,
	[PriceTableName] [varchar](40) NULL,
	[PriceMethod] [varchar](5) NULL,
	[bd01] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd01]  DEFAULT ((0)),
	[bd02] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd02]  DEFAULT ((0)),
	[bd03] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd03]  DEFAULT ((0)),
	[bd04] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd04]  DEFAULT ((0)),
	[bd05] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd05]  DEFAULT ((0)),
	[bd06] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd06]  DEFAULT ((0)),
	[bd07] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd07]  DEFAULT ((0)),
	[bd08] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd08]  DEFAULT ((0)),
	[bd09] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd09]  DEFAULT ((0)),
	[bd10] [decimal](8, 0) NOT NULL CONSTRAINT [DF_BillingPriceTable_bd10]  DEFAULT ((0)),
	[pr01] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr01]  DEFAULT ((0)),
	[pr02] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr02]  DEFAULT ((0)),
	[pr03] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr03]  DEFAULT ((0)),
	[pr04] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr04]  DEFAULT ((0)),
	[pr05] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr05]  DEFAULT ((0)),
	[pr06] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr06]  DEFAULT ((0)),
	[pr07] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr07]  DEFAULT ((0)),
	[pr08] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr08]  DEFAULT ((0)),
	[pr09] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr09]  DEFAULT ((0)),
	[pr10] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr10]  DEFAULT ((0)),
	[pr11] [decimal](11, 5) NOT NULL CONSTRAINT [DF_BillingPriceTable_pr11]  DEFAULT ((0)),
	[modby] [varchar](12) NULL,
	[moddte] [datetime] NULL,
 CONSTRAINT [PK_#BillingPriceTable] PRIMARY KEY CLUSTERED 
(
	[PriceTableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

go
Insert #BillingPriceTable(bd01, bd02, pr01, pr02) Values(7500, 15000, .025, .020);
go
Create Table #Quantities(PriceTableID int, Qty int);
Insert #Quantities(PriceTableID,Qty) Values (1, 7490), (1, 9721);

Select b.PriceTableID, q.qty, Case When q.Qty < b.bd01 Then q.Qty Else b.bd01 End * b.pr01 + 
   Case When q.Qty > b.bd01 Then Case When q.Qty < b.bd02 Then q.Qty - b.bd01 Else b.bd02 - b.bd01 End Else 0 End * b.pr02 +
   Case When q.Qty > b.bd02 Then Case When q.Qty < b.bd03 Then q.Qty - b.bd02 Else b.bd03 - b.bd02 End Else 0 End * b.pr03 +
   Case When q.Qty > b.bd03 Then Case When q.Qty < b.bd04 Then q.Qty - b.bd03 Else b.bd04 - b.bd03 End Else 0 End * b.pr04 +
   Case When q.Qty > b.bd04 Then Case When q.Qty < b.bd05 Then q.Qty - b.bd04 Else b.bd05 - b.bd04 End Else 0 End * b.pr05 +
   Case When q.Qty > b.bd05 Then Case When q.Qty < b.bd06 Then q.Qty - b.bd05 Else b.bd06 - b.bd05 End Else 0 End * b.pr06 +
   Case When q.Qty > b.bd06 Then Case When q.Qty < b.bd07 Then q.Qty - b.bd06 Else b.bd07 - b.bd06 End Else 0 End * b.pr07 +
   Case When q.Qty > b.bd07 Then Case When q.Qty < b.bd08 Then q.Qty - b.bd07 Else b.bd08 - b.bd07 End Else 0 End * b.pr08 +
   Case When q.Qty > b.bd08 Then Case When q.Qty < b.bd09 Then q.Qty - b.bd08 Else b.bd09 - b.bd08 End Else 0 End * b.pr09 +
   Case When q.Qty > b.bd09 Then Case When q.Qty < b.bd10 Then q.Qty - b.bd09 Else b.bd10 - b.bd09 End Else 0 End * b.pr10 +
   Case When q.Qty > b.bd10 Then q.Qty - b.bd10 Else 0 End * b.pr11 As Price
From #BillingPriceTable b
Cross Join #Quantities q
Where b.PriceTableID = q.PriceTableID
go 
Drop Table #BillingPriceTable; 
go
Drop Table #Quantities;
Tom

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 1:14pm

Yes, I am sure it will work but the function is only passed 1 @Qty value and I want to just make 1 pass of 1 row in the BillingPriceTable that already exists.  I will try modifying your query to get the desired price. You have given me some ideas to try.
April 30th, 2015 1:24pm

Does this help?

DECLARE @billingPriceTable TABLE (LowQty INT, HighQty INT, Fee DECIMAL(10,5))
INSERT INTO @billingPriceTable (LowQty, HighQty, fee) VALUES
(0,7500,.025),(7500,15000,.020),(15000,22500,.015)

DECLARE @qty TABLE (qty INT)
INSERT INTO @qty (qty) VALUES 
(7490),(9721),(20000)


SELECT q.qty, SUM((CASE WHEN qty < highqty THEN (qty - LowQty)  ELSE highQty END) * fee) AS totalFee
  FROM @qty q
    INNER JOIN @billingPriceTable bpt
	  ON q.qty > bpt.LowQty
 GROUP BY q.qty

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 1:32pm

I don't need to create tables and group as all the data I need is already in the 1 row returned by the SELECT clause for the existing table named BillingPriceTable.  I simply need to query if the @Qty falls between 0 and bd01 or between bd01+1 and bd02, etc.  The fee needs to accumulate the @Qty * fee for that range and continue from bd01 to bd02, etc. until the @Qty is reached. Kind of crazy.
April 30th, 2015 2:08pm

And that's exactly what my example does. I just provided qty as a table to test multiple possible values at once.

DECLARE @billingPriceTable TABLE (LowQty INT, HighQty INT, Fee DECIMAL(10,5))
INSERT INTO @billingPriceTable (LowQty, HighQty, fee) VALUES
(0,7500,.025),(7500,15000,.020),(15000,22500,.015)

DECLARE @qty INT

SET @qty = 9721


SELECT SUM((CASE WHEN qty < highqty THEN (qty - LowQty)  ELSE highQty END) * fee) AS totalFee
  FROM (SELECT @qty AS qty) q
    INNER JOIN @billingPriceTable bpt
	  ON q.qty > bpt.LowQty
 GROUP BY q.qty

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 2:16pm

Sorry, there is no HighQty and LowQty in the actual table.  It is a very old design that has all the qty ranges in different columns. You can look at my table definition.
April 30th, 2015 2:50pm

But it does!

DECLARE @BillingPriceTable TABLE (PriceTableID int IDENTITY(1,1) NOT NULL, entity varchar(4) NULL, PriceTableCode varchar(10) NULL, PriceTableName varchar(40) NULL, PriceMethod varchar(5) NULL, 
                                  bd01 decimal(8, 0) , bd02 decimal(8, 0) , bd03 decimal(8, 0) , bd04 decimal(8, 0) , bd05 decimal(8, 0) , bd06 decimal(8, 0) , bd07 decimal(8, 0) , bd08 decimal(8, 0) , bd09 decimal(8, 0) , bd10 decimal(8, 0) ,
                                  pr01 decimal(11, 5), pr02 decimal(11, 5), pr03 decimal(11, 5), pr04 decimal(11, 5), pr05 decimal(11, 5), pr06 decimal(11, 5), pr07 decimal(11, 5), pr08 decimal(11, 5), pr09 decimal(11, 5), pr10 decimal(11, 5), pr11 decimal(11, 5),
                                  modby varchar(12) NULL, moddte datetime NULL
                                 )

INSERT INTO @BillingPriceTable (entity, pricetablecode, pricetablename, pricemethod, bd01, bd02, bd03, bd04, bd05, bd06, bd07, bd08, bd09, bd10, pr01, pr02, pr03, pr04, pr05, pr06, pr07, pr08, pr09, pr10, pr11, modby, moddte) VALUES
('xxx','z','zxxz','x',7500,15000,22500,30000,37500,45000,52500,60000,67500,75000,
                      .025,.020, .015, .010, .005, .0,   .0,  .0,    .0,   .0,   .0,
					  'x',CURRENT_TIMESTAMP)

DECLARE @qty INT

SET @qty = 9721

;WITH ohButItDoes AS (
SELECT priceTableID, high-7500 AS lowQty, high AS highqty, fee
  FROM (
        SELECT *
          FROM @BillingPriceTable
       ) s
    UNPIVOT (
	         high FOR val IN ([bd01], [bd02], [bd03], [bd04], [bd05], [bd06], [bd07], [bd08], [bd09], [bd10])
			) p
    UNPIVOT (
	         fee FOR val2 IN ([pr01], [pr02], [pr03], [pr04], [pr05], [pr06], [pr07], [pr08], [pr09], [pr10])
			) f
 WHERE RIGHT(val,2) = RIGHT(val2,2)
)

SELECT SUM((CASE WHEN qty < highqty THEN (qty - LowQty)  ELSE highQty END) * fee) AS totalFee
  FROM (SELECT @qty AS qty) q
    INNER JOIN ohButItDoes bpt
	  ON q.qty > bpt.LowQty
 GROUP BY q.qty

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 3:41pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics