Price calculation based on the unit entered

Question

Table Schema:

CREATE TABLE [dbo].[TblPriceDetails](
    [PriceID] [int] IDENTITY(1,1) NOT NULL,
    [VID] [int] NOT NULL,
    TypeID int not null,
    [RangeStart] [decimal](18, 3) NOT NULL,
    [RangeEnd] [decimal](18, 3) NOT NULL,
    [Price] [decimal](18, 2) NOT NULL,
    [ExtraLoad] [decimal](18, 3) NULL,
    [ExtraPrice] [decimal](18, 2) NULL
)
GO

Sample Data

Insert into dbo.TblPriceDetails values (1,1, 0,0.250,10,0,0)
Insert into dbo.TblPriceDetails values (1,1, 0.251,0.500,15,0.500,15)
Insert into dbo.TblPriceDetails values (1,1, 3,5,40,1,25)
GO
Insert into dbo.TblPriceDetails values (1,2, 0,0.250,15,0,0)
Insert into dbo.TblPriceDetails values (1,2, 0.251,0.500,20,0.500,20)
Insert into dbo.TblPriceDetails values (1,2, 3,5,50,1,30)
GO

Expected Output:

For VID = 1 and TypeID = 1 and a given value 0.300

• As the input unit falls between RangeStart 0.251 and RangeEnd 0.500 the resultant price will be 15

For VID = 1 and TypeID = 1 and a given value 0.600

• As per the data until 0.500 the price is 15 and for every extraLoad of upto 0.500 its another 15. So the final price will be 30

For VID = 1 and TypeID = 1 and given value 1.500

• As per the data until 0.500 the price is 15. For every extra 0.500 its another 15, so for the remaining 1 unit it would be 15 * 2. The final price will be 45

For VID = 1 and TypeID = 1 and given value 5.5

• As per the data until 5.000 the price is 40. For every extra 1 unit its another 25, so the final price will be 65

Need help in writing a query for this. Unlike my other questions I don't have a query yet to show what I have come up with till now. As of now I am not able to frame a logic and come up with a generic query for this.

Answer 1

It looks like you are looking to calculate postage price. The trick is to join on the RangeStart of the next weight tier. LEAD will help you do that:

;WITH
    AdjustedPriceDetails AS
    (
        SELECT      VID, TypeID, RangeStart, RangeEnd, Price, ExtraLoad, ExtraPrice
                ,   ISNULL(LEAD(RangeStart, 1) OVER (PARTITION BY VID, TypeID ORDER BY RangeStart), 1000000) AS NextRangeStart
        FROM        TblPriceDetails
    )


SELECT      T.*
        ,   A.Price + IIF(T.Value <= A.RangeEnd, 0, CEILING((T.Value - A.RangeEnd) / A.ExtraLoad) * A.ExtraPrice)
                                            AS FinalPrice
FROM        #TestData               T
INNER JOIN  AdjustedPriceDetails    A       ON A.RangeStart <= T.Value AND T.Value < A.NextRangeStart

Explanation:

• LEAD(RangeStart, 1) OVER (PARTITION BY VID, TypeID ORDER BY RangeStart) gets the RangeStart of the next row that has the same VID and TypeID
• You will eventually reach the highest weight tier. So ISNULL(..., 1000000) make this tier appear to end at 1M. The 1M is just a stand-in for infinity.

Edit: if you want to make this work with SQL Server 2008, change the CTE:

;WITH
    tmp AS
    (
        SELECT      VID, TypeID, RangeStart, RangeEnd, Price, ExtraLoad, ExtraPrice
                ,   ROW_NUMBER() OVER (PARTITION BY VID, TypeID ORDER BY RangeStart) AS RowNumber
        FROM        TblPriceDetails
    ),
    AdjustedPriceDetails AS
    (
        SELECT      T1.VID, T1.TypeID, T1.RangeStart, T1.RangeEnd, T1.Price, T1.ExtraLoad, T1.ExtraPrice
                ,   ISNULL(T2.RangeStart, 1000000) AS NextRangeStart
        FROM        tmp     T1
        LEFT JOIN   tmp     T2  ON T1.VID = T2.VID AND T1.TypeId = T2.TypeID AND T1.RowNumber + 1 = T2.RowNumber
    )

---

If you wonder what #TestData is (you may not need it)

CREATE TABLE #TestData
(
        VID         int
,       TypeID      int
,       Value       float
)

INSERT INTO #TestData
            ( VID, TypeID, Value)
    VALUES  ( 1, 1, 0.3 )
        ,   ( 1, 1, 0.6 )
        ,   ( 1, 1, 1.5 )
        ,   ( 1, 1, 5.5 )