Dynamic Calculation of a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Calculation of a field

Author

Topic

Baresi
Starting Member

4 Posts

Posted - 2004-07-14 : 22:15:20

Hi,

Was wondering if anyone could give me a hand on this.

I'm developing a quotation system now, and have a problem in the quotation cost calculation.

Basically, this is how the tables are like:

Product
------------
ID
Name
Feature1
Feature2
Feature3
Feature4
Feature4MaxUnits
Feature5
Feature5MaxUnits
Feature6

Quotation
-------------
QuoID
CompanyID
ProductID
Feature1
Feature2
Feature3
Feature4
Feature4Units
Feature5
Feature5Units
Feature6

In Product, Feature1-6 are integer values with the cost of that feature, whilst Feature4Units and 5 are the maximum units of a feature that can be provided on the product.
In quotation, Feature1-6 are checkboxes on the interface and hence bit values in the database.

What I'm trying to do is create a dynamically calculated field, to be stored alongside in 'Quotation', this field being the total quoted price.

So the quoted price will have to retrieve the product record from Product based on ID, then check for each Feature, if it has been selected (check box checked/bit value set?) and where appropriate, the number of units selected by the customer. Simply its the sum of all those individual prices.

Now the problem is I'm totally stumped as the prices can change which affects the quoted price, meaning I can't just calculate at my interface and feed the result into the table.

Any way to do this in the 'insert quotation' stored procedure and store the field as an equation on database fields?

BTW, I'm developing the frontend in asp.net if that matters.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-14 : 23:29:18

SELECT P.ID, P.Name, Q.CompanyID,
Convert(int, Q.Feature1) * P.Feature1 as Feature1Price,
Convert(int, Q.Feature2) * P.Feature2 as Feature2Price,
Convert(int, Q.Feature3) * P.Feature3 as Feature3Price,
CASE WHEN Q.Feature4Units > P.Feature4MaxUnits THEN Q.Feature4Units * P.Feature4 ELSE P.Feature4MaxUnits * P.Feature4 END As Feature4 Price,
CASE WHEN Q.Feature5Units > P.Feature5MaxUnits THEN Q.Feature5Units * P.Feature5 ELSE P.Feature5MaxUnits * P.Feature5 As Feature5 Price,
Convert(int, Q.Feature6) * P.Feature6 as Feature6Price
FROM Product P INNER JOIN Quotation Q on P.ProductID = Q.ProductID

This will give you an individual price for each feature. To get a total, simply add them all up.

btw - Why do you need a Quotation.Feature4 when you've got the Feature4Units? I would have thought that you could just use the latter field, and have it zero if they don't choose anything.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-15 : 12:05:26

I don't know any specifics about your solution, so I may be off base, but I don't like your approach. Any time you see tables that have fields like Thing1,Thing2,Thing2Blah,Thing3,Thing4, etc... it should strike a cord somewhere within you that something is dreadfully wrong.

Consider the below:

[CODE]
CREATE TABLE #qu_Product (
ProductPK int NOT NULL ,
Name varchar (50) NOT NULL
)

CREATE TABLE #qu_Feature (
FeaturePK int NOT NULL ,
ProductFK int NOT NULL ,
Name varchar (50) NOT NULL
)

CREATE TABLE #qu_Feature_MaxUnits (
FeatureFK tinyint NOT NULL ,
MaxUnits int NOT NULL
)

CREATE TABLE #qu_Feature_Price (
FeatureFK tinyint NOT NULL ,
Price smallmoney NULL
)

CREATE TABLE #qu_Quotation (
QuotationPK int NOT NULL ,
CompanyFK int NOT NULL ,
ProductFK int NOT NULL
)

CREATE TABLE #qu_Quotation_Feature (
QuotationFK int NOT NULL ,
FeatureFK int NOT NULL ,
Units int NULL
)

insert #qu_Product (ProductPK,Name)
SELECT 1, 'Car' UNION ALL
SELECT 2,'Boat' UNION ALL
SELECT 3,'House'

insert #qu_Feature(FeaturePK,ProductFK,Name)
SELECT 1,1,'Engine' UNION ALL
SELECT 2,1,'Body' UNION ALL
SELECT 3,1,'Frame' UNION ALL
SELECT 4,1,'Wheels' UNION ALL
SELECT 5,1,'Headlights' UNION ALL
SELECT 6,1,'Interior' UNION ALL
SELECT 7,2,'Boat Motor' UNION ALL
SELECT 8,2,'Deluxe Hull' UNION ALL
SELECT 9,2,'Trailer' UNION ALL
SELECT 10,2,'Trolling Motor' UNION ALL
SELECT 11,3,'Exterior Walls' UNION ALL
SELECT 12,3,'Roof' UNION ALL
SELECT 13,3,'Furnace' UNION ALL
SELECT 14,3,'A/C' UNION ALL
SELECT 15,3,'Rooms' UNION ALL
SELECT 16,3,'Basement'

insert #qu_Feature_MaxUnits (FeatureFK,MaxUnits)
SELECT 4,4 UNION ALL
SELECT 5,2 UNION ALL
SELECT 11,5 UNION ALL
SELECT 15,8

insert #qu_Feature_Price (FeatureFK,Price)
SELECT 1,33.0800 UNION ALL
SELECT 2,21.4700 UNION ALL
SELECT 3,12.9500 UNION ALL
SELECT 4,7.0500 UNION ALL
SELECT 5,3.2900 UNION ALL
SELECT 6,74.1800 UNION ALL
SELECT 7,100.2400 UNION ALL
SELECT 8,54.0100 UNION ALL
SELECT 9,2.0100 UNION ALL
SELECT 10,20.3000 UNION ALL
SELECT 11,40.3300 UNION ALL
SELECT 12,3.5800 UNION ALL
SELECT 13,7.5400 UNION ALL
SELECT 14,13.6800 UNION ALL
SELECT 15,22.4800 UNION ALL
SELECT 16,34.4300

insert #qu_Quotation (QuotationPK,CompanyFK,ProductFK)
SELECT 1,1,1 UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,3 UNION ALL
SELECT 4,1,2

insert #qu_Quotation_Feature (QuotationFK,FeatureFK,Units)
SELECT 1,1,NULL UNION ALL
SELECT 1,2,NULL UNION ALL
SELECT 1,4,NULL UNION ALL
SELECT 2,7,NULL UNION ALL
SELECT 2,8,NULL UNION ALL
SELECT 2,9,NULL UNION ALL
SELECT 3,11,3 UNION ALL
SELECT 3,14,NULL UNION ALL
SELECT 4,7,2 UNION ALL
SELECT 4,10,NULL;

SELECT QuotationPK,
CompanyFK,
p.Name,
COUNT(*) FeatureCount,
SUM(CASE WHEN ISNULL(mu.MaxUnits,1) < qf.Units
THEN ISNULL(mu.MaxUnits,1) ELSE ISNULL(qf.Units,1) END) TotalUnits,
SUM(CASE WHEN ISNULL(mu.MaxUnits,1) < qf.Units
THEN ISNULL(mu.MaxUnits,1) ELSE ISNULL(qf.Units,1) END * fp.Price) TotalCost
FROM #qu_Quotation q
JOIN #qu_Product p
ON q.ProductFK = p.ProductPK
JOIN #qu_Quotation_Feature qf
ON q.QuotationPK = qf.QuotationFK
JOIN #qu_Feature_Price fp
ON fp.FeatureFK = qf.FeatureFK
LEFT JOIN #qu_Feature_MaxUnits mu
ON mu.FeatureFK = qf.FeatureFK
GROUP BY QuotationPK,
CompanyFK,
p.Name;

DROP TABLE #qu_Product;
DROP TABLE #qu_Feature;
DROP TABLE #qu_Feature_MaxUnits;
DROP TABLE #qu_Feature_Price;
DROP TABLE #qu_Quotation;
DROP TABLE #qu_Quotation_Feature;[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources