Member Avatar for iown714

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!

  • 3 Contributors
  • 3 Replies
  • 87 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by fatihpiristine
Member Avatar for debasisdas

Try to create the view using simple where clause for checking the PurchaseDescription field and change the other fields accordingly.

Member Avatar for iown714

Thanks debasisdas for your tip. I will try it.

Member Avatar for fatihpiristine

think sql as calculator

select (cost * quantity) as total from mytable...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.