Select products by total price efficiently

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select products by total price efficiently

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2014-09-08 : 12:40:01

Hello,

I have the following tables:



    create table dbo.Products
    (
      Id int identity not null primary key clustered (Id),	
      Name nvarchar (120) not null,
      Price decimal (19,4) not null
    );

    create table dbo.AllowedQuantities
    (
      Id int identity not null primary key clustered (Id),	
      Quantity float not null
    );

    create table dbo.ProductsAllowedQuantities
    (
      ProductId int not null, 
      QuantityId int not null,
        constraint primary key clustered (ProductId, QuantityId)
    );

    alter table dbo.ProductsAllowedQuantities
    add constraint CProductId foreign key (ProductId) references Products(Id),
		constraint CQuantityId foreign key (QuantityId) references Quantities(Id);

The allowed quantities are 1/3, 1/2, 1, 3/2, 2, 3 and 4.

Given a list of 4 prices, let's say P = { 800, 1000, 1200, 1600 } I need to:

Select 4 random products which total price is in range P - 200 to P + 200.

As an example for P = 1000 the range would be 800 to 1200.

A product with price 500 and allowed quantities 1/2, 1 and 2 would be selected.

This product prices would be 250, 500 and 1000 being the last one accepted.

In conclusion I would need one random product for each of the given prices.

How can I perform this query in the most efficient way possible?

Can I, and should I, change something in my database scheme to improve performance?

Thank You

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 12:53:27

something like this?


	with p(p) as (
		select 800,1000,1200,1600)
	select *
	from (select name, sum(price*quantity) totalprice from dbo.Products 
		  join dbo.ProductsAllowedQuantities paq
		    on products.id = paq.ProductId
		  join dbo.AllowedQuantities aq
		    on paq.QuantityId = aq.Quantity
		  group by name) sub
	join p on p.p = sub.totalprice

Note: if you post some sample data (as insert into statements) and expected results, we can work on it more easily

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2014-09-08 : 13:58:23

quote:
Originally posted by gbritton
Note: if you post some sample data (as insert into statements) and expected results, we can work on it more easily

I just created the code:


create table dbo.Products
(
  Id int identity not null primary key clustered (Id),  
  Name nvarchar (120) not null,
  Price decimal (19,4) not null
);

create table dbo.AllowedQuantities
(
  Id int identity not null primary key clustered (Id),  
  Quantity float not null
);

create table dbo.ProductsAllowedQuantities
(
  ProductId int not null, 
  QuantityId int not null,
    constraint ProductIdQuantityId primary key clustered (ProductId, QuantityId)
);

alter table dbo.ProductsAllowedQuantities
add constraint CProductId foreign key (ProductId) references Products(Id),
    constraint CQuantityId foreign key (QuantityId) references AllowedQuantities(Id);

insert into dbo.AllowedQuantities (Quantity) VALUES (0.5), (1), (2)

insert into dbo.Products (Name, Price) VALUES ('A', 400), ('B', 500), ('C', 800), ('D', 1000), ('E', 1200), ('F', 1400)

insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (1, 1), (1, 2)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (2, 1)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (3, 1), (3, 3)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (4, 2), (4, 3)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (5, 1), (5, 3)
insert into dbo.ProductsAllowedQuantities (ProductId, QuantityId) VALUES (6, 2)

Then I tried the following query:


select p.Name, p.Price, aq.Quantity, TotalPrice = p.Price * aq.Quantity
from dbo.Products as p
join dbo.ProductsAllowedQuantities as paq
on p.Id = paq.ProductId
join dbo.AllowedQuantities aq
on paq.QuantityId = aq.Quantity

Which would result in:

Name - Price - Quantity - TotalPrice
A - 400.0000 - 1 - 400
A - 400.0000 - 2 - 800
B - 500.0000 - 1 - 500
C - 800.0000 - 1 - 800
D - 1000.0000 - 2 - 2000
E - 1200.0000 - 1 - 1200
F - 1400.0000 - 2 - 2800

Note 1
One thing that puzzles me is that I can't see the quantity 0.5.

If the given quantity is P = 700 then the range is 500 to 900.

So the second, third and four record would apply.

I would get one of this records randomly.

And if I would have two given prices: P = { 700, 1900 }

Then the ranges would be [500, 900] and [1700, 2100]

So for the second range I would have the product D with quantity 2.

Note 2
Would be possible that for each given price the selected product would be different, if possible, of course ...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 17:11:40

OK...so with the data you just posted, what do you want to see as output? I have to admit that your mention of ranges is puzzling. How is that supposed to work?

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2014-09-09 : 07:34:29

quote:
Originally posted by gbritton

OK...so with the data you just posted, what do you want to see as output? I have to admit that your mention of ranges is puzzling. How is that supposed to work?

Let me update my code because it had a mistake:


select p.Name, p.Price, aq.Quantity, TotalPrice = p.Price * aq.Quantity
from dbo.Products as p
join dbo.ProductsAllowedQuantities as paq
on p.Id = paq.ProductId
join dbo.AllowedQuantities aq
on paq.QuantityId = aq.Id

Which would result in the following:

Name - Price - Quantity - TotalPrice
A - 400.0000 - 0,5 - 200
A - 400.0000 - 1 - 400
B - 500.0000 - 0,5 - 250
C - 800.0000 - 0,5 - 400
C - 800.0000 - 2 - 1600
D - 1000.0000 - 1 - 1000
D - 1000.0000 - 2 - 2000
E - 1200.0000 - 0,5 - 600
E - 1200.0000 - 2 - 2400
F - 1400.0000 - 1 - 1400

I can try to better explain what I am doing. I have:


create table dbo.Groups
(
  Id int identity not null primary key clustered (Id),  
  Name nvarchar (120) not null
);

create table dbo.GroupsPrices
(
  Id int identity not null primary key clustered (Id),  
  GroupId int not null,
  Price float not null
);

Each group has 4, 5 ou 6 Group Prices. No less and no more.

So given a Group Id, for example 1, I get its Group Prices, let's say:

prices = { 400, 800, 1200, 1600, 2000 }

Then I calculate the ranges according to a rule, for example: +/- 100:

ranges = { [300, 500], [700, 900], [1100, 1300], [1500, 1700], [1900, 2100] }

Then for each range I need to get one random product which TotalPrice is within the range.

So the sequence is:

1 - Get a Group by Group Id
2 - Get the Group Prices (it will be 4, 5 ou 6 prices. It depends of the group.)
3 - For each price calculate a range (using +/- X the price. Let's use 100)
4 - Select one random product which total price is in each of the ranges.
5 - So I will end up with 4, 5 ou 5 products. If possible all different.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources