Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to retrieve a column depending on variable value? Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 10:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 3, 2013 1:41 PM
Points: 1, Visits: 7
Hi guys, I have a problema that I can't find a clue to face it:

The situation:
I have three different tables for showing product prices as according:
Customer Info
Product Info
Product Price per Customer

Where:
- The customer info table stores the customerId and the name
- The product info stores the product info like productId, productDescription, the family to which the product belongs to AND 10 different prices (price1, price2, price3 and so on)
- The product Price per customer stores the customerId, the productFamily and the level Price assigned (price1, price2 ... price10)

So, the point is, that a customer can have different Price levels depending on the family, let's say we have three different families:

Soda
Candies
Cigarrettes

And Customer 'John Doe' have assigned price1 for Soda Products, price2 for Candies and no Price assigned (normal Price) for Cigarrettes.

That means that in the database we will have the following info:

in Customer table:
CustomerId: 5487
customerName: 'John Doe'

in Product Info:
ProductId | name | familyName | listPrice | price1 | price2 | price3 | price4 | price5
1 | Pepsi | Soda | 10 | 9 | 8 | 7 | 6 | 5
2 | Coke | Soda | 10 | 9 | 8 | 7 | 6 | 5
3 | Fanta | Soda | 10 | 9 | 8 | 7 | 6 | 5
4 | 7-Up | Soda | 10 | 9 | 8 | 7 | 6 | 5
5 | Pepsi | Soda | 10 | 9 | 8 | 7 | 6 | 5
6 | Marlb | Cigarrettes | 10 | 9 | 8 | 7 | 6 | 5
7 | Camel | Cigarrettes | 10 | 9 | 8 | 7 | 6 | 5
8 | 7-Up | Soda | 10 | 9 | 8 | 7 | 6 | 5
9 | Pepsi | Soda | 10 | 9 | 8 | 7 | 6 | 5
10 | Supe | Candies | 10 | 9 | 8 | 7 | 6 | 5
11 | Sugar | Candies | 10 | 9 | 8 | 7 | 6 | 5

Usually, the normal query to retrieve product's prices could be:

SELECT * FROM productPrice

Now the problem:

I need to retrieve productId, productName, familyName and the Price assigned to the customer
so, in the case of John Doe, I need to retrieve something like

ProductId | name | familyName | priceAssigned
1 | Pepsi | Soda | 9
2 | Coke | Soda | 9
3 | Fanta | Soda | 9
4 | 7-Up | Soda | 9
5 | Pepsi | Soda | 9
6 | Marlb | Cigarrettes | 10
7 | Camel | Cigarrettes | 10
8 | 7-Up | Soda | 10
9 | Pepsi | Soda | 10
10 | Supe | Candies | 8
11 | Sugar | Candies | 8

Because John have assigned price1 for soda family product, price2 for candies products and pricelist (no assigned Price) for cigarrettes products

Now, there's any clue how can I run a query that fills the column depending on the Price assigned?

Problem 1: The way to know which column Price I need to show to the customer is by querying the Price per customer table, Price per customer stores info like following:

customerId | familyName | priceAssigned
5423 | Soda | price1
5423 | Candies | price2

I was considering to créate a temp table and fill the right Price according to the family Price assigned, but it will consume a lot of performance, so, I was hoping someone out there could give me some light...

Any help will be appreciated, I accept suggestions on perform a query, créate functions or whatever :)

Post #1435758
Posted Wednesday, March 27, 2013 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
Welcome to the forum Henry.

As this is quite an involved problem, may I suggest that you check out the link in my signature and then post some consumable DDL and DML to set up test tables, data & show sample results.

If you take the time to do this, I am sure that someone will help you.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1435793
Posted Wednesday, April 3, 2013 5:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:44 AM
Points: 1,127, Visits: 1,592
You can do it using Dynamic sql as follows:

--Setting up Sample Data 

Create Table Ex
(
ProductId Int,
name Varchar(20),
familyName Varchar(20),
listPrice Int,
price1 Int,
price2 Int,
price3 Int,
price4 Int,
price5 Int
)

Insert Into Ex
Select 1, 'Pepsi', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 2, 'Coke', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 3, 'Fanta', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 4, '7-Up', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 5, 'Pepsi', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 6, 'Marlb', 'Cigarrettes', 10, 9, 8, 7, 6, 5
Union ALL
Select 7, 'Camel', 'Cigarrettes', 10, 9, 8, 7, 6, 5
Union ALL
Select 8, '7-Up', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 9, 'Pepsi', 'Soda', 10, 9, 8, 7, 6, 5
Union ALL
Select 10, 'Supe', 'Candies', 10, 9, 8, 7, 6, 5
Union ALL
Select 11, 'Sugar', 'Candies', 10, 9, 8, 7, 6, 5


--Query for your requirement

Declare @Soda Varchar(10), @Candies Varchar(10), @Cigarrettes Varchar(10), @sql Varchar(MAX)
Select @Soda = 'price1', @Candies = 'price2', @Cigarrettes = 'NULL'
Select @sql = 'Select ProductId, Name, familyName, (Case When familyName = ''Soda'' Then COALESCE(' + @Soda + ',listPrice)
When familyName = ''Candies'' Then COALESCE(' + @Candies + ',listPrice)
When familyName = ''Cigarrettes'' Then COALESCE(' + @Cigarrettes + ',listPrice)
Else ''''
END) As PriceAssigned
From Ex'
Execute(@sql)



Hope this was what you were looking for.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1438280
Posted Wednesday, April 3, 2013 6:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
SELECT pi.ProductId, pi.name, pi.familyName, 
CASE pp.priceAssigned
WHEN 'listPrice' THEN pi.listPrice
WHEN 'price1' THEN pi.price1
WHEN 'price2' THEN pi.price2
WHEN 'price3' THEN pi.price3
WHEN 'price4' THEN pi.price4
WHEN 'price5' THEN pi.price5
END AS [priceAssigned]
FROM Customer c
JOIN ProductPricePerCustomer pp ON pp.CustomerId = c.CustomerId
JOIN ProductInfo pi ON pi.familyName = pp.familyName




Far away is close at hand in the images of elsewhere.

Anon.

Post #1438292
Posted Thursday, April 4, 2013 11:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
If a sixth column/price gets added or a new product in the family, with 'price 5' rather than 'price5' you will miss data, have to keep maintaining the query. That is the tough part about working with a design that is not normalised.
Post #1438938
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse