|
|
Posted by serge on 12/03/05 22:59
How can I run a single SP by asking multiple sales question either
by using the logical operator AND for all the questions; or using
the logical operator OR for all the questions. So it's always
either AND or OR but never mixed together.
We can use Northwind database for my question, it is very similar
to the structure of the problem on the database I am working on.
IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL
DROP TABLE REPORT_SELECTION
GO
CREATE TABLE REPORT_SELECTION
(
AUTOID INT IDENTITY(1, 1) NOT NULL,
REPSELNO INT NOT NULL, -- Idenitifies which report query this
-- "sales question" is part of
SupplierID INT NOT NULL, -- from the Suppliers table
ProductID INT NOT NULL, -- from the Products table, if you choose
--a ProductID, SupplierID is selected also by inheritence
CategoryID INT NOT NULL, -- from the Categories table
SOLDDFROM DATETIME NULL, -- Sold from which date
SOLDTO DATETIME NULL, -- Sold to which date
MINSALES INT NOT NULL, -- The minimum amount of sales
MAXSALES INT NOT NULL, -- The maximum amount of sales
OPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR
)
GO
INSERT INTO REPORT_SELECTION
SELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALL
SELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1
You can ask all kinds of sales questions like:
1-I want all employees that sold products from supplierID 1
(Exotic Liquids), specifically the ProductID 2 (Chang) from the
CategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and sold
between $10 and $10000 - AND for my 2nd sales question
2-I want all employees that sold CategoryID 1 (beverages) between
Jan 1 1996 to Jan 1 2000 and sold between $10 and $1000
I want to get the common result of both questions and find out
which employee(s) are in this list.
Here are some of the points:
1-I want my query to return the list of employees fitting the
result of my sales question(s).
2-If I ask three questions with the logical operator AND, I want
the list of employees that are common to all three questions.
3-If I ask 2-3-4. questions with the logical operator OR, I want
the list of employees that are in the list of the 1st "successful"
sales question (the first question that returns any employee is
good enough)
4-You can ask all kind of sales question you want even if they
contradict each other. The SP should still run and return
nothing if that is the case.
5-Let's assume you can have the same product name from the same
supplier but under different categories. So entering a ProductID
should not automatically enter the CategoryID also; whereas
entering the ProductID should automatically enter its SupplierID.
6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatory
fields, you can't leave them NULL
7-SupplierID, ProductID and CategoryID are the dynamic input
parameters, there can be 5 different combinations to choose from:
a-SupplierID only
b-SupplierID and a ProductID,
c-SupplierID and a CategoryID
d-SupplierID, ProductID and a CategoryID
e-CategoryID only
f-Any time you choose a ProductID, the SupplierID value
will be filled automatically based on the ProductID's
relationship
g-Any of the three values here that is not chosen by the
user will take a default value of -1 (meaning return ALL
for this Column, in other words don't filter by this column)
The major problem I have is I can't use dynamic SQL for choosing
the three dynamic columns as the 2nd row of records would have a
different selection of dynamic columns (at least I don't know how
if the solution is dynamic SQL). The only solution I can think of
looks pretty bad to me. I would use a cursor, run each row at a
time, store a TRUE, FALSE value to stop processing or not and
store the result in another detail table. Then if all AND
questions have ended with TRUE do a union of all the result and
return the common list of employees. It sounds pretty awful as an
approach. I am hoping there's a simpler method for achieving this.
Does anyone know if any SQL book has a topic on this type of
query? If so I'll definitely buy the book.
I appreciate any help you can provide.
Thank you
Navigation:
[Reply to this message]
|