Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Question Unanswered: How to multiply value in a field

    Hello, I have a quick question. I'm currently using MS SQL 2005.

    I have a main table which has many fields. I created a view#1 which imports all the rows

    from the main table but I only select the "City" field, "PurchaseDescription" field and a

    "Cost" field. Only the city name is unique. There are a total of 50 different city names,

    and each name can have more than one "PurchaseDescription" each with an associated "Cost". I

    would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to

    contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to

    "USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the

    "PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as

    "Cost".


    For example,

    The view#1 will have the following rows & fields (I had to pad the field with dots just to

    make the output look viewable on this thread)

    City..............PurchaseDescription.........Cost
    --------------------------------------------
    LA.................desk........................... ..4.5
    LA.................USB............................ ..5.0
    LA.................USB............................ ..6.0
    SD................chair........................... ...4.0
    SD................door............................ ..10.0

    The view#2 should have the following rows and fields


    City..............PurchaseDescription.........Cost .......Cost2
    ---------------------------------------------------------
    LA.................desk........................... ..4.5....... 4.5
    LA.................USB............................ ..5.0....... -5.0
    LA.................USB............................ ..6.0....... -6.0
    SD................chair........................... ...4.0....... 4.0
    SD................door............................ ..10.0....... 10.0

    I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need

    a final view that would look like view#2.

    Could you please help if you can?

    Thank you very much for your time and have a safe Halloween!

  2. #2
    Join Date
    Oct 2007
    Posts
    5
    I think that this code can halp you. I use this to simulate you case:

    Code:
    create table #t
    (
    city varchar(20)
    ,PurchaseDescription varchar(20)
    ,cost decimal(5,1)
    )
    
    insert into #t 
     values('LA','desk',4.5)
    insert into #t 
    values('LA','USB',5.0)
    insert into #t 
    values('LA','USB',6.0)
    insert into #t 
    values('SD','chair',4.0)
    insert into #t 
    values('SD','door',10.0)
    
    /************* select as view#1*******************/
    select * from #t
    
    /************* select as view#2*******************/
    select *,CASE PurchaseDescription 
                          WHEN 'USB' 
    	              THEN COST *(-1)
                          ELSE COST 
                END AS COST2
    FROM #t
    
    DROP TABLE #t
    It's easy, isn't it?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by iown714
    I had to pad the field with dots just to make the output look viewable on this thread
    You can use [ code ] tags (without the spaces between code and the brackets) for this.

  4. #4
    Join Date
    Nov 2007
    Posts
    2
    Thank you mik3sh and shammat for your help and tips.

    mik3sh,
    I tried your code and it worked! Thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •