I've come up with a solution; However, because I have no experience with PostgreSQL and currently have access only to Microsoft SQL Server, you'll have to determine if the technique can be translated and applied to PostgreSQL.
In order to make this work, I had to add Identity / Primary Key columns to the DiscountMaster and Sales tables.
DiscountMaster:
Sales:
You can see that I've also added a fifth Sales row (Levi's/Denim). I did this to determine what would happen when NO matching discounts exist.
I created a scalar (returns a single value) SQL function; func_SalesDiscountID, which takes a SalesID value argument and returns a DiscountMasterID of the best matching discount or NULL if there is no matching discount for the requested Sales row. Here's the SQL script that creates the SQL function:
-- Drop the function (if it exists) before creating it...
IF OBJECT_ID('func_SalesDiscountID') IS NOT NULL
DROP FUNCTION dbo.func_SalesDiscountID
go
CREATE FUNCTION dbo.func_SalesDiscountID(@salesid int)
RETURNS int
AS
BEGIN
-- This function returns the ID of the DiscountMaster row that best matches the requested Sales row.
-- Note: If there are no matches, the function returns NULL.
RETURN
(SELECT t2.DiscountMasterID
FROM (SELECT TOP 1
t1.DiscountMasterID,
t1.Ranking,
MAX(t1.ranking) AS MaxRanking
FROM (SELECT s.SalesID,
s.Variety,
s.Brand,
s.Model,
s.Pattern,
s.Shade,
s.Material,
s.Finish,
s.Size,
dm.DiscountMasterID,
dm.DiscountPercent,
-- Increment ranking whenever any Sales value matches a DiscountMaster value...
CASE WHEN s.Variety IS NOT NULL AND s.Variety = dm.Variety THEN 1 ELSE 0 END
+CASE WHEN s.Brand IS NOT NULL AND s.Brand = dm.Brand THEN 1 ELSE 0 END
+CASE WHEN s.Model IS NOT NULL AND s.Model = dm.Model THEN 1 ELSE 0 END
+CASE WHEN s.Pattern IS NOT NULL AND s.Pattern = dm.Pattern THEN 1 ELSE 0 END
+CASE WHEN s.Shade IS NOT NULL AND s.Shade = dm.Shade THEN 1 ELSE 0 END
+CASE WHEN s.Material IS NOT NULL AND s.Material = dm.Material THEN 1 ELSE 0 END
+CASE WHEN s.Finish IS NOT NULL AND s.Finish = dm.Finish THEN 1 ELSE 0 END
+CASE WHEN s.Size IS NOT NULL AND s.Size = dm.Size THEN 1 ELSE 0 END AS Ranking
FROM dbo.Sales s
CROSS JOIN dbo.DiscountMaster dm --Tests all discounts against all Sales rows
WHERE s.SalesID = @salesid) AS t1
GROUP BY t1.DiscountMasterID,
t1.Ranking
HAVING t1.Ranking > 0 --Ensures no matches results in zero discount.
ORDER BY t1.Ranking DESC) AS t2)
END
go
GRANT EXECUTE ON dbo.func_SalesDiscountID TO PUBLIC
go
Some comments regarding the SQL function:
(1) It was not necessary to include all of the Sales columns in the innermost SELECT, but this was helpful when I was constructing/developing the overall SQL, so I've left it in for illustrative purposes.
(2) The "ranking" value is calculated by summing the result of each CASE/END statement, each of which produces 1 if the Sales column value matches the corresponding DiscountMaster column value, or zero if the Sales column value is NULL or does not match.
(3) Note that a cartesian product (Cross Join) is used to check the requested Sales row against every DiscountMaster row. Always take care when requesting a cartesian product!
Once the function is created and execution rights are granted, the query to produce the results is straightforward:
SELECT s.Variety,
s.Brand,
s.Model,
s.Pattern,
s.Shade,
s.Material,
s.Finish,
s.Size,
--No discount, if no matching DiscountMaster found.
ISNULL(dm.DiscountPercent,0.000) AS DiscountPercent
FROM dbo.Sales s
-- A left outer join is required because there may be no
-- DiscountMaster rows that apply to a Sales row.
LEFT OUTER JOIN dbo.DiscountMaster dm ON
(dbo.func_SalesDiscountID(s.SalesID) = dm.DiscountMasterID)
The above query produces the following results:
Disclaimer: I make no claim this solution is optimal or that it handles tie-breaker conditions adequately, since Sivaprakash provided no information concerning tie-breakers. Tie-breaking is currently addressed via the "TOP 1" condition in conjunction with the ORDER BY clause in the SQL function definition.
Obviously this is not a PB problem, as Sivaprakash indicated at the onset, but it is an interesting and challenging problem nonetheless.
Regards, John
