March 26, 2013 at 10:14 pm
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 🙂
March 27, 2013 at 2:09 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2013 at 5:53 am
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.
April 3, 2013 at 6:30 am
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.
April 4, 2013 at 11:42 am
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.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply