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.)
AgentIDIncomePartnerIDPartner_IncomeDate
------
A000015000A00002500021/03/2005
A000025000A00003500022/04/2005
A000045000A00005500020/05/2005
A000035000A00002500031/03/2005
A000065000A00001500001/01/2005
A000075000A00021500001/01/2005
A000085000A00033500001/01/2005
(ETC)
Table: AgentsParticulars (Contains Details On Agents)
AgentIDNameDepartmentStatusManagerID
------
A00001ErnieResidentialActiveNULL
A00002BenResidentialActiveA00001
A00003KeithResidentialActiveA00001
A00004BillResidentialActiveA00002
A00005CrystalResidentialActiveA00002
A00006JeanResidentialActiveA00003
A00007JoshuaResidentialActiveA00031
(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
AgentIDNameDepartmentStatusTotalIncom TotalTeamIncome
------A00002BenResidentialActive15000
10000
A00003KeithResidentialActive10000 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.googlegro ups.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.
>
|||Thanks Uri. I did a Stored Procedure with something to this extent.
However, as the database stores a few thousand records for each person,
the time taken to retrieve the records online is extremely slow. So, was
just wondering if there is a workaround it. The main prob I face here is
that the initial design of the database wasn't good. So, with that prob,
it has created a mountain of other problems. Yups. Oh, by the way, I'm
using SQL 7, so, FUNCTION don't really work for me. Thanks for your
reply though. Really appreciate it.
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry, did not read properly that you are using SQL Server 7.0
However , you can re-write the UDF as a Stored Procedure as you did probably
and having properly defined indexeses you'll not have any problems in terms
of performance ( a few thousand records for each person is really small
amount of data)
"Ernie Song" <ernie.song@.orangetee.com> wrote in message
news:OzoOFau$FHA.1408@.TK2MSFTNGP15.phx.gbl...
> Thanks Uri. I did a Stored Procedure with something to this extent.
> However, as the database stores a few thousand records for each person,
> the time taken to retrieve the records online is extremely slow. So, was
> just wondering if there is a workaround it. The main prob I face here is
> that the initial design of the database wasn't good. So, with that prob,
> it has created a mountain of other problems. Yups. Oh, by the way, I'm
> using SQL 7, so, FUNCTION don't really work for me. Thanks for your
> reply though. Really appreciate it.
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||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.