Problem with SQL Script with Clarion Date format

I need to run a sql script to update records in a table that were created
that day. I have a huge database and it would take a while to run a script
on all.
Here's what i have so far: (this is a select stmt version not update stmt
i'm using for testing)
SELECT tm5user.matter.c_date as CreateDate,
((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
FROM tm5user.matter
INNER JOIN tm5user.billopt ON tm5user.matter.sysid = tm5user.billopt.owner_id
WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
I got the script to work but it doesn't calculate the date correctly. The
date field "c_date" is based on a clarion base date of 12/28/1800.
I have my select statement return the "c_date" field, the calculated date
field, and full table columns for testing. The CreateDate and CalcDate
should be equal and they are not.
Does anyone have any ideas ... I tried CastDate that also did not work.
Any help or tips would be great ..... thanks.
rob bartley
msce> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate
The above expression (1800-12-28) is doing integer arithmetic. I suggest you use the DATEDIFF or
DATEADD function (depending on what you want to achieve). Also, I suggest you format the date in
language neutral format ('yyyymmdd') so it doesn't break in a nationalized environment.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <temp@.dstek.com> wrote in message news:us1cOsGtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I need to run a sql script to update records in a table that were created
> that day. I have a huge database and it would take a while to run a script
> on all.
> Here's what i have so far: (this is a select stmt version not update stmt
> i'm using for testing)
> SELECT tm5user.matter.c_date as CreateDate,
> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
> FROM tm5user.matter
> INNER JOIN tm5user.billopt ON tm5user.matter.sysid => tm5user.billopt.owner_id
> WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
>
> I got the script to work but it doesn't calculate the date correctly. The
> date field "c_date" is based on a clarion base date of 12/28/1800.
> I have my select statement return the "c_date" field, the calculated date
> field, and full table columns for testing. The CreateDate and CalcDate
> should be equal and they are not.
> Does anyone have any ideas ... I tried CastDate that also did not work.
> Any help or tips would be great ..... thanks.
> rob bartley
> msce
>

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.

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 ManagerI
D
----
---
A00001 Ernie Residential Active NULL
A00002 Ben Residential Active A00001
A00003 Keith Residential Active A00001
A00004 Bill Residential Active A00002
A00005 Crystal Residential Active A0000
2
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 TotalInco
m 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.
>|||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.sql

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.

Problem with Provider=SQLXMLOLEDB.3.0

Hi!

When I make a connection to the sqlserver database, everything work
fine.
Recently I connected to a huge db 1.4 GB.
The querys are executed with the same time however making a connection
is taking a long time.

I am using the following connection string.

My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
"Data Provider=SQLOLEDB;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=" & MY_SERVER & ";" & _
"Initial Catalog=BigDB;" & _
"User Id=sa;" & _
"Password=sa"

Thanks is advance,
JaiJai (jaijai_kumar@.hotmail.com) writes:
> When I make a connection to the sqlserver database, everything work
> fine.
> Recently I connected to a huge db 1.4 GB.
> The querys are executed with the same time however making a connection
> is taking a long time.
> I am using the following connection string.
> My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
> "Data Provider=SQLOLEDB;" & _
> "Network Library=DBMSSOCN;" & _
> "Data Source=" & MY_SERVER & ";" & _
> "Initial Catalog=BigDB;" & _
> "User Id=sa;" & _
> "Password=sa"

Take out Network Library from the connection string. Not sure if it
helps, but I don't think that DBMSOCN is one of the preferred onces.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot for ur message...
It did not help?
Any other idea...?

Thanks,
Jai

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94934E9BA02EYazorman@.127.0.0.1>...
> Jai (jaijai_kumar@.hotmail.com) writes:
> > When I make a connection to the sqlserver database, everything work
> > fine.
> > Recently I connected to a huge db 1.4 GB.
> > The querys are executed with the same time however making a connection
> > is taking a long time.
> > I am using the following connection string.
> > My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
> > "Data Provider=SQLOLEDB;" & _
> > "Network Library=DBMSSOCN;" & _
> > "Data Source=" & MY_SERVER & ";" & _
> > "Initial Catalog=BigDB;" & _
> > "User Id=sa;" & _
> > "Password=sa"
> Take out Network Library from the connection string. Not sure if it
> helps, but I don't think that DBMSOCN is one of the preferred onces.|||Jai (jaijai_kumar@.hotmail.com) writes:
> Thanks a lot for ur message...
> It did not help?
> Any other idea...?

There was not an overflow of information in your post, so it is
difficult to suggest anything. But let's look at it again.

>> > When I make a connection to the sqlserver database, everything work
>> > fine.
>> > Recently I connected to a huge db 1.4 GB.
>> > The querys are executed with the same time however making a connection
>> > is taking a long time.

Is the database that is slow to connect to on the same server as
the one that is fast to connect to? (My assumption is that is not.)

If you connect to the slow server from Query Analyzer, does that
also take time? If that is fast, what if you connect with SQLOLEDB
only; that is "Provider=SQLOLEDB"?

Assuming that you connect slow no matter the method, the problem may
be in the network somewhere. Not really my field of expertise to
say what that may be. Did you try connecting by IP-addrress directly?

Finally, most people would find a 1.4GB a moderately size database,
or even smallish...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp