Count average cost and update cost of all products in column with conditions

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • gelezniyden
    New Member
    • Mar 2013
    • 14

    Count average cost and update cost of all products in column with conditions

    Hi guys, please help me with following:

    I have table "Products" and have following task: Count evarege cost and update cost of all products in column "UnitPrice" with conditions if current price > everage then -10% if < +20%

    By the way need to use variables

    Please help me to write script I use SQL server 2000

    Thanks everybody before.
  • r035198x
    MVP
    • Sep 2006
    • 13225

    #2
    What have you tried?

    Comment

    • gelezniyden
      New Member
      • Mar 2013
      • 14

      #3
      Somthing like that but don't know how to resume

      Code:
      DECLARE @Average cost
      
      SELECT @Average cost = AVG(UnitPrice)  FROM Products  
      
      
      Update Products
      
      Set UnitPrice = @Average cost
      
      DECLARE @Computing
      
      IF
      Last edited by Rabbit; Mar 25 '13, 03:54 PM. Reason: Please use code tags when posting code.

      Comment

      • r035198x
        MVP
        • Sep 2006
        • 13225

        #4
        Here is a tip: You can use a CASE clause in an update statement

        e.g
        Code:
        UPDATE someTableName 
        SET someColumnName = CASE WHEN (someColumnName < someValue) THEN value1 ELSE value2 END
        Now try it again.

        Comment

        • gelezniyden
          New Member
          • Mar 2013
          • 14

          #5
          Didn't get you buddy sorry I'm not very good in Transact SQL I've wrote somthing like below:

          Code:
          DECLARE @Average cost
          
          SELECT @Average cost = AVG(UnitPrice)  FROM Products  
          
          
          Update Products
          
          Set UnitPrice = @Average cost
          
          DECLARE @Computing
          
          IF UnitPrice > @Average cost SET@Computing= -10%
          
          ELSE SET IF UnitPrice < @Averaeg cost SET@Computing = +20%
          Could you please correct my statment or modefy your according my conditions?

          Comment

          • r035198x
            MVP
            • Sep 2006
            • 13225

            #6
            Don't make variable names with spaces. Use AVERAGE_COST instead of Average cost for the variable name and indicate a type.


            In your update you are setting UnitPrice to @Average which is not what you described above. Your description says:

            "update cost of all products in column "UnitPrice" with conditions if current price > everage then -10% if < +20%" So your statement should look like
            Code:
            UPDATE Products
            SET UnitPrice = CASE WHEN UnitPrice > @Average THEN <your logic here> ELSE <your other logic here> END
            Trust me it's better for you learn how to come up with this yourself than have someone do it for you.

            You can find more SQL server examples and tutorials here:http://msdn.microsoft.com/en-us/library/ms188927.aspx

            Comment

            • gelezniyden
              New Member
              • Mar 2013
              • 14

              #7
              I wrote following:
              Code:
              UPDATE Products
              SET UnitPrice = CASE WHEN UnitPrice > @Average_cost THEN -10% ELSE UnitPrice < @Average_cost THEN + 20%
              Please correct if somthing is wrong

              Comment

              • r035198x
                MVP
                • Sep 2006
                • 13225

                #8
                What is -10% supposed to be? It's 10% of what? Also you can't just write plain English. You need to learn and use the correct SQL syntax. Hint: 10% of a value is the same as multiplying a value by 0.1

                Comment

                • gelezniyden
                  New Member
                  • Mar 2013
                  • 14

                  #9
                  Sorry buddy just can't understand it logically, anyway thank you for your help

                  Comment

                  • r035198x
                    MVP
                    • Sep 2006
                    • 13225

                    #10
                    Just go through your question again and understand exactly what it's asking for. Think about how you would do it manually on a piece of paper until you really understand what's required. Then go through this thread again and see if it starts to make sense.

                    Comment

                    • gelezniyden
                      New Member
                      • Mar 2013
                      • 14

                      #11
                      Tried below:

                      Code:
                      DECLARE @Average_cost money(8)
                      
                      SELECT @Average_cost = AVG(UnitPrice)  FROM Products 
                      
                      UPDATE Products
                      SET UnitPrice = (CASE WHEN UnitPrice > @Average_cost 
                                            THEN UnitPrice - UnitPrice/10 
                                            ELSE UnitPrice < @Average_cost 
                                            THEN UnitPrice - UnitPrice/20                      
                                      END)
                      It says Incorrect syntax near '<' I don't understand where I was wrong

                      Comment

                      • r035198x
                        MVP
                        • Sep 2006
                        • 13225

                        #12
                        Look at the structure of the examples I posted and compare it with what you have.

                        Comment

                        • gelezniyden
                          New Member
                          • Mar 2013
                          • 14

                          #13
                          Code:
                          DECLARE @Average_cost money
                          
                          SELECT @Average_cost = AVG(UnitPrice)  FROM Products  
                          UPDATE Products
                          SET UnitPrice = (CASE WHEN UnitPrice > @Average_cost 
                                                THEN UnitPrice - (UnitPrice * .1)
                                                WHEN UnitPrice < @Average_cost 
                                                THEN UnitPrice + (UnitPrice * .2)  
                                                ELSE @Average_cost                   
                                          END)
                          Thank you for help I applied statment above and it was helpful for me. Issue was resolved.

                          Comment

                          • r035198x
                            MVP
                            • Sep 2006
                            • 13225

                            #14
                            Well done!

                            Comment

                            Working...