Problem With SQL

Hi all. I have a huge complex problem:
Table: salesTran (List all the sames transactions. One transaction may
have two parties involved.)
AgentID Income PartnerID Partner_Income Date
------
A00001 5000 A00002 5000 21/03/2005
A00002 5000 A00003 5000 22/04/2005
A00004 5000 A00005 5000 20/05/2005
A00003 5000 A00002 5000 31/03/2005
A00006 5000 A00001 5000 01/01/2005
A00007 5000 A00021 5000 01/01/2005
A00008 5000 A00033 5000 01/01/2005
(ETC)
Table: AgentsParticulars (Contains Details On Agents)
AgentID Name Department Status ManagerID
------
A00001 Ernie Residential Active NULL
A00002 Ben Residential Active A00001
A00003 Keith Residential Active A00001
A00004 Bill Residential Active A00002
A00005 Crystal Residential Active A00002
A00006 Jean Residential Active A00003
A00007 Joshua Residential Active A00031
(ETC)
What I wanted to do was to create a Stored Procedure so that when I
pass an AgentID into in, it'll return me the next level agent details
as follows:
ID Passed In: A00001
AgentID Name Department Status TotalIncom TotalTeamIncome
------A00002 Ben Residential Active 15000
10000
A00003 Keith Residential Active 10000 5000
The TotalIncome column will display all income of the agent (Total
Income + Total PartnerIncome). The TotalTeamIncome will display the sum
of the totalIncome of all agents linked (refer to the AgentsParticulars
table). So, in the case of A00002, the TotalTeamIncome will display the
total sum of the total income of A00002 and A00005 (and any agent that
has A00002 and A00005 as their manager).
Anyone has any workarounds on this? I'm using SQL 7.0.
Thanks so much.Ernie
Look atv this script written by Itzik Ben-Gan
Perhars it is not exactly what you wanted but I'm sure it gives you an idea
to solve the problem
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Ernie" <ernie.song@.orangetee.com> wrote in message
news:1134355686.843988.65790@.g43g2000cwa.googlegroups.com...
> Hi all. I have a huge complex problem:
> Table: salesTran (List all the sames transactions. One transaction may
> have two parties involved.)
> AgentID Income PartnerID Partner_Income Date
> ------
> A00001 5000 A00002 5000 21/03/2005
> A00002 5000 A00003 5000 22/04/2005
> A00004 5000 A00005 5000 20/05/2005
> A00003 5000 A00002 5000 31/03/2005
> A00006 5000 A00001 5000 01/01/2005
> A00007 5000 A00021 5000 01/01/2005
> A00008 5000 A00033 5000 01/01/2005
> (ETC)
> Table: AgentsParticulars (Contains Details On Agents)
> AgentID Name Department Status ManagerID
> ------
> A00001 Ernie Residential Active NULL
> A00002 Ben Residential Active A00001
> A00003 Keith Residential Active A00001
> A00004 Bill Residential Active A00002
> A00005 Crystal Residential Active A00002
> A00006 Jean Residential Active A00003
> A00007 Joshua Residential Active A00031
> (ETC)
> What I wanted to do was to create a Stored Procedure so that when I
> pass an AgentID into in, it'll return me the next level agent details
> as follows:
> ID Passed In: A00001
> AgentID Name Department Status TotalIncom TotalTeamIncome
> ------A00002
> Ben Residential Active 15000
> 10000
> A00003 Keith Residential Active 10000 5000
>
> The TotalIncome column will display all income of the agent (Total
> Income + Total PartnerIncome). The TotalTeamIncome will display the sum
> of the totalIncome of all agents linked (refer to the AgentsParticulars
> table). So, in the case of A00002, the TotalTeamIncome will display the
> total sum of the total income of A00002 and A00005 (and any agent that
> has A00002 and A00005 as their manager).
> Anyone has any workarounds on this? I'm using SQL 7.0.
> Thanks so much.
>|||Well I couldnt help but notice one thing that could be a serious problem for
this and other things you would want to do with this table: it is badly
denormalized. The partner income column is 100% redundant and should be
completely removed...
It doesnt seem right what you have going with the partnerID column either in
terms of normalization. It would make your procedure easier and would help
normalize your DB if you split this into 2 tables.