March 12, 2009 at 2:35 pm
This seems like it should be very easy, but it's eluding me. I have 3 tables:
Price
PriceID | UnitPrice
1 | 1.99
2 | 3.99
PriceGroup
PriceID | DescriptionID
1 | 1
1 | 2
1 | 3
2 | 1
2 | 3
2 | 4
and
Description
DescriptionID | Code
1 | Cu
2 | Fe
3 | Y
4 | E
What would be the select statement to figure out the price of a "combination" description? For example, how would I get the price of Cu-Fe-Y (1.99)? Again, this seems like it should be easy, but I'm hitting a wall (repeatedly)...
Thanks.
March 12, 2009 at 2:45 pm
Start with this and go from there:
declare @Price table (
PriceID int,
UnitPrice money
);
declare @PriceGroup table (
PriceID int,
DescriptionID int
);
declare @Description table (
DescriptionID int,
Code varchar(2)
);
insert into @Price
select 1, 1.99 union all
select 2, 3.99;
insert into @PriceGroup
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3 union all
select 2,4;
insert into @Description
select 1,'Cu' union all
select 2,'Fe' union all
select 3,'Y' union all
select 4,'E';
select
*
from
@Price p
inner join @PriceGroup pg
on (p.PriceID = pg.PriceID)
inner join @Description d
on (pg.DescriptionID = d.DescriptionID)
I'm not exactly sure what you are trying to get from the tables based on your post.
March 12, 2009 at 2:45 pm
SELECT SUM(UnitPrice)
FROM Description d INNER JOIN PriceGroup pg
ON d.DescriptionID = pg.DescriptionID
INNER JOIN Price p ON pg.PriceID = p.PriceID
WHERE d.Code IN ('Cu','Fe','Y')
Are there certain combinations you are needing? How is this query being used? Generally you get a list and would summarize on the front end...
Cheers,
Brian
March 12, 2009 at 3:00 pm
Yeah, my question was a bit vague; Sorry about that.
The basic operation of this will (should) be:
1. Customer selects a combination of 3 attributes, as shown in the Description.Code column.
2. The customer will get back the unit price from the Price table. The price for the "combination" of the choices is defined in the pricegroup table. For example, if The combination of Cu, Fe, Y was selected, the customer should see 1.99. If Cu, Fe, E was selected, the price should be 3.99, and so on.
Hope that's a bit clearer.
Thanks.
March 12, 2009 at 3:05 pm
pklug (3/12/2009)
Yeah, my question was a bit vague; Sorry about that.The basic operation of this will (should) be:
1. Customer selects a combination of 3 attributes, as shown in the Description.Code column.
2. The customer will get back the unit price from the Price table. The price for the "combination" of the choices is defined in the pricegroup table. For example, if The combination of Cu, Fe, Y was selected, the customer should see 1.99. If Cu, Fe, E was selected, the price should be 3.99, and so on.
Hope that's a bit clearer.
Thanks.
Okay, start with the code I provided and see if you can come up with a solution. If not, post the code you are trying to write and let us see what we can do based on that.
March 12, 2009 at 3:36 pm
Well, this works, but it somehow seems flimsy. Is there a better way of doing this?
declare @Price table (
PriceID int,
UnitPrice money
);
declare @PriceGroup table (
PriceID int,
DescriptionID int
);
declare @Description table (
DescriptionID int,
Code varchar(2)
);
declare @temp table (
priceid int,
pricecount int
);
insert into @Price
select 1, 1.99 union all
select 2, 3.99;
insert into @PriceGroup
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3 union all
select 2,4;
insert into @Description
select 1,'Cu' union all
select 2,'Fe' union all
select 3,'Y' union all
select 4,'E';
insert into @temp
select priceid, count(priceid) as Pricecount from @PriceGroup pg
inner join @Description d
on (pg.DescriptionID = d.DescriptionID)
where d.Code in ('Cu', 'Fe', 'Y')
group by priceid
select unitprice from @price where priceid = (select top 1(priceid) from @temp order by pricecount desc)
March 13, 2009 at 9:12 am
Unfortunately what you have does not work. Try it with the other criteria you stated earlier, it still returns 1.99.
Also, unfortunately, I don't have time right now to look at it in more depth.
March 13, 2009 at 9:27 am
If I run
declare @Price table (
PriceID int,
UnitPrice money
);
declare @PriceGroup table (
PriceID int,
DescriptionID int
);
declare @Description table (
DescriptionID int,
Code varchar(2)
);
declare @temp table (
priceid int,
pricecount int
);
insert into @Price
select 1, 1.99 union all
select 2, 3.99;
insert into @PriceGroup
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 2,4;
insert into @Description
select 1,'Cu' union all
select 2,'Fe' union all
select 3,'Y' union all
select 4,'E';
insert into @temp
select priceid, count(priceid) as Pricecount from @PriceGroup pg
inner join @Description d
on (pg.DescriptionID = d.DescriptionID)
where d.Code in ('Cu', 'Fe', 'E')
group by priceid
select unitprice from @price where priceid = (select top 1(priceid) from @temp order by pricecount desc)
i get 3.99, which is right.
The problem is, if a combination is entered that is not explicitly defined in PriceGroup (let's say CU, Y, E), the query will return a value which doesn't necessarily apply to the selection. I guess that's what I meant by "flimsy".
Anyway, you've helped me already, so a "thank you" is in order.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply