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