Expression or Statement Help Please

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

General SQL Server Forums

New to SQL Server Programming

Expression or Statement Help Please

Author

Topic

jd343
Starting Member

8 Posts

Posted - 2011-12-09 : 14:50:22

Hello

This is my first post in this wonderful forum.
I am new to programming and sql

I am finding it hard to understand how to do the following excercise from a tutorial.

I dont understand what I should be using to work this out or how to do it, as I say I am very

new to this

I have a datafeed, in which I have a
cost price (column 12) (from a supplier)
weight of an item (column 15)

Now the supplier charges shipping based on weight for example if an item is

0.001 - 1.000 kg then £7
1.001 - 2.000 kg then £9
2.001 - 5.000 kg then £11

So in order to work out my total cost price of an item I need to check the weight and then get

the right shipping price and then add that to the cost price of the item.

After that I need to add a profit margin of 3% and vat of 20%

for example a product price is £3.00 and weight is 1.500 kg

total cost price would be £3.00 + £9.00 = £12.00

Then add profit margin 3%

£12.00 * 1.03 = £12.36

Then add vat rate 20%

£12.36 * 1.20

My sell price would be

£14.83

Basically the application I am using will take the raw file and data process it and then post

the changes afterwards.

How can I do this in SQL please.
Kind Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-09 : 17:15:49

Is the amount to charge based on weight in a table or is that hard coded?

jd343
Starting Member

8 Posts

Posted - 2011-12-09 : 17:34:23

Hello There

Thank you for the kind reply,

The amount to charge is based on fixed prices given beforehand 0.001 - 1.000 kg then £7
1.001 - 2.000 kg then £9
2.001 - 5.000 kg then £11

so they have to be added into the SQL statement.

The cost price (column 12) and weight (column 15) values are in the .csv datafeed file in 2 seperate columns

So something like

price weight kg
[CSV_COL(12)] [CSV_COL(15)]

£3.50 2.000
£10.00 0.500
£7.50 7.000

The statement has to be written in SQL and the application then calculates the values on local computer before posting the changes to the site

so it gets value from column 12 then checks the value in column 15
then adds both together and then the profit margin 3% and then vat 20%

Hope it makes things clearer if not please let me know

Kind Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-09 : 17:42:17

Here is one way (with sample data!!!)

DECLARE @Foo TABLE(cost_price MONEY, item_weight DECIMAL (10,5))

INSERT @Foo (cost_price, item_weight)
VALUES
($3.00, 1.5),
($75.98, 2.72),
($1.12, 0.09),
($1000.00, 1.67),
($5.00, 4.0)


SELECT
    (
        ( 
            cost_price 
            + CASE 
                WHEN item_weight BETWEEN 0.001 AND 1.000 THEN 7 
                WHEN item_weight BETWEEN 1.001 AND 2.000 THEN 9 
                WHEN item_weight BETWEEN 2.001 AND 5.000 THEN 11
                ELSE NULL -- unknown weight
              END
         )   
         * 1.03 -- Margin
    )* 1.20 -- VAT
FROM 
    @Foo

jd343
Starting Member

8 Posts

Posted - 2011-12-10 : 10:22:17

Thank you for the kind reply. much apreciated.

Just to clarify that the values are in a csv file and the application allows to create SQL Statement in Expression

It uses the csv column number as reference to perform calculation in this case the price is in [CSV_COL(12)] and weight is in [CSV_COL(15)]

I have tried to subsitute where you have written cost_price and item_weight with the [CSV_COL(12)] [CSV_COL(15)] but the application returns error when processing

Cannot evaluate expression

I would upload the csv file but cant see where I can do that on here

I think your calculation will work just matter of doing it in way the application understands it. would appreciate your advice

Once again thank you for your kind reply, much apprciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources