Expression or Statement Help Please

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

to calculate shipping cost, you'd use a CASE expression

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 price and 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

gee, i wish i could see your entire query from here, but unfortunately my cheap warez version of Microsoft® CrystalBall© is down at the moment

hint: post your query

Hello Again thank you for the reply,

I will try and explain it in words rather than query.

[CSV_COL(15)] represents weight column in the csv file
[CSV_COL(12)] represents price column in the csv file

If [CSV_COL(15)] is between 0 and 1.00
Then [CSV_COL(12)] + 7

If [CSV_COL(15)] is between 1.01 and 2
Then [CSV_COL(12)] + 9

If [CSV_COL(15)] is between 2.01 and 5
Then [CSV_COL(12)] + 11

Then need to get whichever condition is true above then add 3% commission and then 20% VAT (tax)

But I don’t understand how to write it in SQL correctly

Hope this clarifies what I am trying to achieve if not please let me know

Once again thank you for the reply.

Regards

did you try it the way i showed you with that CASE expression?

if so, please post your query

Hello

This is what I get when the data parses in the application

Cannot evaluate expression "SELECT items.id
, items.descr
, items.price
, CASE WHEN items.weight < 1.000 THEN 7
WHEN items.weight < 2.000 THEN 9
WHEN items.weight < 5.000 THEN 11
ELSE 937
END AS shipping_cost
FROM items" for field "Price" with value "42.79". Error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT items.id , items.descr , items.price , CASE WHEN items.weight' at line 1"

how about if you would please run that query outside your application, directly in mysql

Hello Again

Thank you for the reply,

I will try to learn how to do this I am total newbie, I have asked the developers if it is possible for the application to use an external database either on a server or on local hard drive in calculations.

The only thing is that I have more than 1 supplier which offer weight based shipping, so I have to do it so each feed uses its own database,

I was also shown a method of using a Foo Table which I think is like a Temporary Table used in calculations. But again there was a syntax error

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

Also the values used have to match the column names in the csv feed.

I would appreciate any ideas or advice on where to start or anywhere I could find an tutorial or example of doing this.


Kind Regards

Kind Regards

Another Possible Option would be.

Provide each supplier with a unique id i.e. supp1, supp2, supp3

And perform the calculations on the server database,



SELECT suppID.id

, items.price
, CASE WHEN items.weight < 1.000 THEN 7
WHEN items.weight < 2.000 THEN 9
WHEN items.weight < 5.000 THEN 11
ELSE 937
END AS shipping_cost
FROM items

I guess I would have to do this for each supplier I create. As each will have there own prices.

Just a thought would appreciate if anyone can add

Kind Regards

a couple of points --

first, it appears that you are using mysql, and so (as a moderator) i'm moving this thread to the mysql forum

second, the code you were given for the @foo table is obviously intended for a microsoft sql server database (mysql does not have a MONEY datatype), so maybe wherever you got it from, they didn't realize you were actually using mysql either

the reason i asked you to run your query outside of your application is because it died on the first word it came to, which suggests that the database call was somehow malformed

do you have a front end utility program like phpmyadmin or navicat or heidisql? something that would let you run your query directly in mysql?

if shipping varies by supplier, then yes, you need a second table besides your items table

Once again thank you for your kind reply,

I will try in phpmyadmin

I need to create seperate tables for each supplier however how do I differentiate between the suppliers - I can use unique sku for products so supplier A =
supa194839
supa194844
supa184753

Supplier B =
supb194839
supb194334
supb149385

However, when the data is sent to the server, I need the code to check which supplier a product belongs to

Then check it against the weight of the item then add the shipping charge to the cost price of item, or at the end. (end would be better)

This way a visitor could add different items belonging to different suppliers and get a weight based total for each supplier

So
3 items from Supplier A = Total Weight 4kg = £8
2 items from Supplier B = Total Weight 2kg = £6

total shipping = £14


Is this correct? What kind of statement or query is required?

If yes then how long will it take approx if we have thousands of products for each supplier?


Any help or suggestions most appreciated

Kind Regards

actually, i don't think you do

as for the other stuff, i don't know what's right, only you can decide what order to do the calculations in