October 20, 2003 at 12:38 pm
Hi forum,
I have a very interresting and therefore very complicated problem:
Given the following tables
publisher(
pu_pu_id int not null primary key,
pu_pu_name varchar(50) not null,
pu_def_discount money not null
)
products(
pd_id int not null primary key,
pd_name not null,
pd_group int null,
pd_price money not null,
pd_pu_id int not null
)
bookstore(
bs_id int not null primary key,
bs_group int null,
bs_name varchar(50) not null
)
discount(
di_id int not null primary key,
di_pd_id int null,
di_pd_group int null,
di_bs_id int null,
di_bs_group null,
di_pu_id null,
di_startdate datetime not null,
di_enddate datetime not null,
di_discount_rate money not null,
di_priority int not null
)
The problem is that we have to give the list of products with the actual price (getdate() between discount.di_startdate and discount.di_enddate) valid for the given bookstore (identified by bookstore.bs_id). basically the booksore wants to download a complete pricelist with the actual prices valid only for him.
The following rules must be respected:
- a publisher has a default discount (publisher.pu_def_discount) which has to taken into account if no other is given in the discount table
- a product COULD belong to a product group but not necessairly
- a bookstore COULD belong to a bookstore group (bookstore.bs_group) but not necessairly
- if in the discount table more then one records satisfy the search criteria then the highest priority should be taken (discount.di_priority)
if there are several same priority the we have to take only the first one (top 1)
- A SINGLE SELECT QUERY IS REQUIRED NO STORED PROCEDURE IS ACCEPTED!
- NO TEMPORARY TABLE USAGE IS ACCEPTED!
- NO CURSOR IS ACCEPTED!
- every other technics, like derived tables, views ... are accepted
My problem is to get back a list of products with the right discount rate and therefore price. It' easy to do that for a single product-bookstore. The only thing which makes my life difficult is the dicount table where several records could satisfy my criteria and I only have to list each product once!
For a single publisher, product, bookstore here is the result (if no result retourned the default discount rate is taken. see above):
create proc GetPct @publisher int, @shopper int, @shopDiscGrp int, @product int, @prdDiscGrp int
as
Selecttop 1
di_discount_rate,
di_priority
Fromdiscount
Wheredi_puref = @publisher
And(di_bsref = @shopper or di_bgref = @shopDiscGrp or di_bsref is null and di_bgref is null)
And(di_prrfnbr = @product or di_pgref = @prdDiscGrp or di_prrfnbr is null and di_pgref is null)
Andgetdate() between di_startdate and di_enddate
order
Bydi_priority desc,
di_pct desc
That one must be done for all the products for a given bookstore
Any idea?
Bye
Gabor
Bye
Gabor
October 20, 2003 at 3:10 pm
Based on your stored procedure, I take it the priority is actually (di_Priority DESC, di_discount_rate DESC). Couldn't you just use something like this?
DECLARE @bs_Id int
SET @bs_Id = 1
SELECT p.pd_Id, p.pd_Name, p.pd_Price,
(SELECT TOP 1 Disc
FROM
(SELECT di_Discount_Rate disc, di_Priority
FROM Discounts d
WHERE (di_pd_Id = p.pd_Id OR di_pd_Group = p.pd_Group
OR di_bs_Id = b.bs_Id OR di_bs_Group = b.bs_Group
OR di_pu_Id = u.pu_Id)
AND LEFT(GETDATE(),11) BETWEEN di_StartDate AND di_EndDate
UNION
SELECT pu_Def_Discount, -1
FROM Publishers
WHERE pu_id = u.pu_id)x
ORDER BY di_Priority DESC, disc DESC) Disc
FROM Products p JOIN Publishers u ON u.pu_Id = p.pd_pu_Id, Bookstores b
WHERE b.bs_Id = @bs_Id
--Jonathan
--Jonathan
October 21, 2003 at 6:52 am
This is slightly faster, simpler, and doesn't assign an artificial priority to the publisher default discount:
SELECT p.pd_Id, p.pd_Name, p.pd_Price,
ISNULL(
(SELECT TOP 1 Disc
FROM
(SELECT di_Discount_Rate disc, di_Priority
FROM Discounts d
WHERE (di_pd_Id = p.pd_Id OR di_pd_Group = p.pd_Group
OR di_bs_Id = b.bs_Id OR di_bs_Group = b.bs_Group
OR di_pu_Id = u.pu_Id)
AND LEFT(GETDATE(),11) BETWEEN di_StartDate AND di_EndDate) x
ORDER BY di_Priority DESC, disc DESC)
, u.pu_Def_Discount) Disc
FROM Products p JOIN Publishers u ON u.pu_Id = p.pd_pu_Id, Bookstores b
WHERE b.bs_Id = @bs_Id
--Jonathan
--Jonathan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply