#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    to calculate shipping cost, you'd use a CASE expression
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by nrt453
    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
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    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
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by nrt453
    But I don’t understand how to write it in SQL correctly
    did you try it the way i showed you with that CASE expression?

    if so, please post your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    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"
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by nrt453
    This is what I get when the data parses in the application
    how about if you would please run that query outside your application, directly in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    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
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    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
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    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



    Originally Posted by r937
    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
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by nrt453
    I need to create seperate tables for each supplier however
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo