Help optimising a stored proc

Hi I have the following procedure that accepts two CSV lists of values,
the first list contains primary key numbers, and the second values to
update.
When the list gets over about 200 items, I am getting intermittent
Timout errors.
Currently just over 500,000 records in the table.
Is there a way to optimise the performace of the update? Or is theer an
easier way if the input can be provided in a single list
e.g 2245=1,2257=2,3367=2 instead of
2245,2257,3367 and 1,2,2
Thanks!
CREATE Procedure dbo.UpdateResults
@.RegistrationIDs Varchar(8000),
@.Results Varchar(4000)
AS
UPDATE tblRegistrations
SET Result
= (SELECT A.Value FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value )
WHERE EXISTS (SELECT *
FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value)
CREATE FUNCTION dbo.Split
(
@.List varchar(8000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(
Id int identity(1,1),
Value nvarchar(150)
)
AS
BEGIN
While (Charindex(@.SplitOn,@.List)>0)
Begin
Insert Into @.RtnValue (value)
Select
Value =
ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
Set @.List =
Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
End
Insert Into @.RtnValue (Value)
Select Value = ltrim(rtrim(@.List))
Return
ENDI would try using temporary tables instead of table variables. I have seen
some strange stuff happen when trying to join two table variables together,
or joining a table-valued function together with a real base table.
Move the logic of the dbo.split funtion inside the stored procedure, take
the input csv strings and write them into a temporary table, then process
from that temp table.
"hals_left" wrote:

> Hi I have the following procedure that accepts two CSV lists of values,
> the first list contains primary key numbers, and the second values to
> update.
> When the list gets over about 200 items, I am getting intermittent
> Timout errors.
> Currently just over 500,000 records in the table.
> Is there a way to optimise the performace of the update? Or is theer an
> easier way if the input can be provided in a single list
> e.g 2245=1,2257=2,3367=2 instead of
> 2245,2257,3367 and 1,2,2
> Thanks!
>
> CREATE Procedure dbo.UpdateResults
> @.RegistrationIDs Varchar(8000),
> @.Results Varchar(4000)
> AS
> UPDATE tblRegistrations
> SET Result
> = (SELECT A.Value FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value )
> WHERE EXISTS (SELECT *
> FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value)
>
> CREATE FUNCTION dbo.Split
> (
> @.List varchar(8000),
> @.SplitOn nvarchar(5)
> )
> RETURNS @.RtnValue table
> (
> Id int identity(1,1),
> Value nvarchar(150)
> )
> AS
> BEGIN
> While (Charindex(@.SplitOn,@.List)>0)
> Begin
> Insert Into @.RtnValue (value)
> Select
> Value =
> ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
> Set @.List =
> Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
> End
> Insert Into @.RtnValue (Value)
> Select Value = ltrim(rtrim(@.List))
> Return
> END
>|||Hi There,
What Mark suggested is right but you may like to try this .
UPDATE T1
SET Result = A.Value
>From FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
Join tblRegistrations T1 On T1.RegistrationID=B.Value
and remove the split function If possible .
With Warm regards
Jatinder Singh
Mark Williams wrote:
> I would try using temporary tables instead of table variables. I have seen
> some strange stuff happen when trying to join two table variables together
,
> or joining a table-valued function together with a real base table.
> Move the logic of the dbo.split funtion inside the stored procedure, take
> the input csv strings and write them into a temporary table, then process
> from that temp table.
> --
> "hals_left" wrote:
>|||Thanks Jatinder, that works nice on a small test and is much simpler
SQL.
I'l test in on the real database and see how it compares to the
original
I dont really see how can I remove the split function, unless I pass
the data 1 record at a time in a loop ...

Help on multiple date range on sql statement

Using SQLServer ver 7.0, two tables:
TableA = contains all inventory data
TableB = contains four fields: ID, source, date_from, date_to
This is where multiple range of dates are populated.
Sample 1:
1,'A','9/1/2004','9/30/2004'

Sample 2:
2,'A','1/1/2003','3/31/2003'
3,'A','10/1/2004','10/31/2004'

Data populated on TableB varies.

Sample SQL for Sample 1:
SELECT *
FROM TableA
WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
DATE_TO from TableB)

Problem: How to approach sql statement based on Sample 2 above?B (no_spam@.no_spam.com) writes:
> Using SQLServer ver 7.0, two tables:
> TableA = contains all inventory data
> TableB = contains four fields: ID, source, date_from, date_to
> This is where multiple range of dates are populated.
> Sample 1:
> 1,'A','9/1/2004','9/30/2004'
> Sample 2:
> 2,'A','1/1/2003','3/31/2003'
> 3,'A','10/1/2004','10/31/2004'
> Data populated on TableB varies.
>
> Sample SQL for Sample 1:
> SELECT *
> FROM TableA
> WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
> DATE_TO from TableB)

SELECT *
FROM TableA A
JOIN TableB B ON B.ID = A.ID
WHERE A.inventory_date BETWEEN B.date_from ABD B.date_to

But this is really a guess. If this does not answer your question, please
post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The expected result given the sample data.

That makes it possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 8 Nov 2004 22:40:44 -0500, B wrote:

>Using SQLServer ver 7.0, two tables:
>TableA = contains all inventory data
>TableB = contains four fields: ID, source, date_from, date_to
>This is where multiple range of dates are populated.
>Sample 1:
>1,'A','9/1/2004','9/30/2004'
>Sample 2:
>2,'A','1/1/2003','3/31/2003'
>3,'A','10/1/2004','10/31/2004'
>Data populated on TableB varies.
>
>Sample SQL for Sample 1:
>SELECT *
>FROM TableA
>WHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (select
>DATE_TO from TableB)
>Problem: How to approach sql statement based on Sample 2 above?

Hi B,

If you want it to return all inventory details with an inventory_date
between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
and 10/31/2004, try this query:

SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM TableA AS A
INNER JOIN TableB AS B
ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is exactly solution I needed.

Many thanks for your time!
Bob

> If you want it to return all inventory details with an inventory_date
> between 1/1/2003 and 3/31/2003 or with an inventory date between 10/1/2004
> and 10/31/2004, try this query:
> SELECT A.Column1, A.Column2, ..., A.ColumnN
> FROM TableA AS A
> INNER JOIN TableB AS B
> ON A.inventory_date BETWEEN B.DATE_FROM and B.DATE_TO
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Help on a Query

I have a table (ResidentList) which includes the following fields
- ID (autonumber)
- Address2 (text)
- Area (text)
Address2 usually contains an apartment number like "122 GV" but is always
blank when Area is "Duplex"
Area contains a string like "Glen View", "Northwind", "Duplex", ec.
I want a query to return the count of unique values for the combination of
(Address2 + Area) and the total count of IDs. The result must be grouped by
Area
I get all tangled up trying to code the count of unique values of Address2 +
Area. I think I am approaching it the wrong way. To get just the count of
IDs grouped by Area I use the following query which works fine but when I
start trying to add the unique values of Address2 + Area I can't find the
correct syntax.
SELECT ResidentList.Area, Count(ResidentList.ID) AS CountOfID
FROM ResidentList
GROUP BY ResidentList.Area
ORDER BY Count(ID) DESC;
--
Any suggestions are appreciated.Try this:
SELECT ResidentList.Area, ResidentList.Address2, Count(ResidentList.ID) AS
CountOfID
FROM ResidentList
GROUP BY ResidentList.Area, ResidentList.Address2
ORDER BY Count(ID) DESC;
ML
http://milambda.blogspot.com/|||I appreciate the response but that results in a row for every value of
Address2. I want the distict count
Wayne
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A44A7033-F87F-4C4B-B713-149331B17AA6@.microsoft.com...
> Try this:
> SELECT ResidentList.Area, ResidentList.Address2, Count(ResidentList.ID) AS
> CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area, ResidentList.Address2
> ORDER BY Count(ID) DESC;
>
> ML
> --
> http://milambda.blogspot.com/|||Can you post the DDL for this and some sample data
"Wayne Wengert" wrote:

> I have a table (ResidentList) which includes the following fields
> - ID (autonumber)
> - Address2 (text)
> - Area (text)
> Address2 usually contains an apartment number like "122 GV" but is always
> blank when Area is "Duplex"
> Area contains a string like "Glen View", "Northwind", "Duplex", ec.
> I want a query to return the count of unique values for the combination of
> (Address2 + Area) and the total count of IDs. The result must be grouped b
y
> Area
> I get all tangled up trying to code the count of unique values of Address2
+
> Area. I think I am approaching it the wrong way. To get just the count of
> IDs grouped by Area I use the following query which works fine but when I
> start trying to add the unique values of Address2 + Area I can't find the
> correct syntax.
> --
> SELECT ResidentList.Area, Count(ResidentList.ID) AS CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area
> ORDER BY Count(ID) DESC;
> --
> Any suggestions are appreciated.
>
>|||Sorry - Should have included that in the original post... I needed to
obscure some personal information first.
========================================
=====
CREATE TABLE [ResidentList] (
[ID] [int] NOT NULL ,
[LastName] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(1,
'Burton', 'Betty', '2101 S. Podunk Ave', '139 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-9024', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(2,
'Parker', 'Bonnie', '2101 S. Podunk Ave.', '138 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-6738', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(3,
'Galloway', 'Richard', '2101 S. Podunk Ave.', '126 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-3213', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(4,
'Martin', 'Ruth A.', '2113 S. Podunk Ave.', NULL, 'SomeCity', 'CA', '12345',
'Duplex', '555-1855', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(5,
'Adams', 'Ike', '2101 S. Podunk Ave.', '234 B', 'SomeCity', 'CA', '12345',
'Boulevard', '555-9679', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(6,
'Anderson', 'Barbara', '2101 S. Podunk Ave.', '039 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-1286', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(7,
'Bachman', 'Joe', '2101 S. Podunk Ave.', '252 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-5012', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(8,
'Bailey', 'Richard', '2101 S. Podunk Ave.', '156 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-6454', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(9,
'Baker', 'Jewell', '2101 S. Podunk Ave.', '136 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-37778', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(10,
'Ballard', 'Margie', '2101 S. Podunk Ave.', '043 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-4623', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(11,
'Benson', 'Helen Louise', '2101 S. Podunk Ave.', '409 AL', 'SomeCity', 'CA',
'12345', 'Assisted Living', '555-0684', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(12,
'Bernhardt', 'Irene', '2101 S. Podunk Ave.', '130 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-0771', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(13,
'Bigge', 'CArrine', '2101 S. Podunk Ave.', '121 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-4052', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(14,
'Bischoff', 'Donna', '2101 S. Podunk Ave.', '135 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-3739', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(15,
'Bishop', 'Ruth', '2101 S. Podunk Ave.', '204 AL', 'SomeCity', 'CA',
'12345', 'Assisted Living', '555-9241', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(16,
'Blomberg', 'Ruth', '2101 S. Podunk Ave.', '226 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-9772', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(17,
'Boeke', 'Adah', '2101 S. Podunk Ave.', '427 BS', 'SomeCity', 'CA', '12345',
'Boulevard South', '555-5359', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(18,
'Bower', 'Dawn', '2101 S. Podunk Ave.', '137 GV', 'SomeCity', 'CA', '12345',
'Garden View', '555-2020', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(19,
'Brennesholt', 'Evelyn', '2101 S. Podunk Ave.', '315 AL', 'SomeCity', 'CA',
'12345', 'Assisted Living', '613-1558', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(20,
'Brown', 'Roberta', '2101 S. Podunk Ave.', '038 GV', 'SomeCity', 'CA',
'12345', 'Garden View', '555-8590', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(21,
'Brownlee', 'Jo', '2101 S. Podunk Ave.', '433 B', 'SomeCity', 'CA', '12345',
'Boulevard', '555-4432', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(22,
'Bruce', 'William', '2101 S. Podunk Ave.', '158 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0161', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(23,
'Bruce', 'Thelma', '2101 S. Podunk Ave.', '158 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0161', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(24,
'Bucklen', 'Helen', '2101 S. Podunk Ave.', '258 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0327', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(25,
'Bucklen', 'Sharon', '2101 S. Podunk Ave.', '258 BH', 'SomeCity', 'CA',
'12345', 'Blue Heron', '555-0327', NULL)
Insert into [ResidentList] ([ID], [LastName], [FirstName], [Address1],
[Address2], [City], [State], [ZIP], [Area], [Phone], [Email]) values(26,
'Callahan', 'Margaret', '2101 S. Podunk Ave.', '322 B', 'SomeCity', 'CA',
'12345', 'Boulevard', '555-8198', NULL)
========================================
=====
"Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
news:18A0273D-337D-4E90-916C-52EDA2A0DA3C@.microsoft.com...
> Can you post the DDL for this and some sample data
> "Wayne Wengert" wrote:
>|||try Count(distinct (Area+Address2))?
YH
"Wayne Wengert" wrote:

> I have a table (ResidentList) which includes the following fields
> - ID (autonumber)
> - Address2 (text)
> - Area (text)
> Address2 usually contains an apartment number like "122 GV" but is always
> blank when Area is "Duplex"
> Area contains a string like "Glen View", "Northwind", "Duplex", ec.
> I want a query to return the count of unique values for the combination of
> (Address2 + Area) and the total count of IDs. The result must be grouped b
y
> Area
> I get all tangled up trying to code the count of unique values of Address2
+
> Area. I think I am approaching it the wrong way. To get just the count of
> IDs grouped by Area I use the following query which works fine but when I
> start trying to add the unique values of Address2 + Area I can't find the
> correct syntax.
> --
> SELECT ResidentList.Area, Count(ResidentList.ID) AS CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area
> ORDER BY Count(ID) DESC;
> --
> Any suggestions are appreciated.
>
>|||I had tried that. I get an error that "distinct" is an undefined function in
the expression?
"YH" <YH@.discussions.microsoft.com> wrote in message
news:904262EB-0A7F-4111-99FA-123CF4CB36D3@.microsoft.com...
> try Count(distinct (Area+Address2))?
> YH
> "Wayne Wengert" wrote:
>|||Is this what you are looking at
SELECT ResidentList.Area, Count(DISTINCT ISNULL(ResidentList.Address2,''))
AS CountOfID
FROM ResidentList
GROUP BY ResidentList.Area
ORDER BY ResidentList.Area
Assisted Living 3
Blue Heron 5
Boulevard 6
Boulevard South 1
Duplex 1
Garden View 8
- Sha Anand
"Wayne Wengert" wrote:

> I had tried that. I get an error that "distinct" is an undefined function
in
> the expression?
>
> "YH" <YH@.discussions.microsoft.com> wrote in message
> news:904262EB-0A7F-4111-99FA-123CF4CB36D3@.microsoft.com...
>
>|||Sha;
Interesting approch but when I try it I get a "Syntax error (missing
operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'
Wayne
"Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
news:F235F5F4-BD81-49FA-AFF6-CDD04ED92CC7@.microsoft.com...
> Is this what you are looking at
>
> SELECT ResidentList.Area, Count(DISTINCT ISNULL(ResidentList.Address2,''))
> AS CountOfID
> FROM ResidentList
> GROUP BY ResidentList.Area
> ORDER BY ResidentList.Area
> Assisted Living 3
> Blue Heron 5
> Boulevard 6
> Boulevard South 1
> Duplex 1
> Garden View 8
>
> - Sha Anand
> "Wayne Wengert" wrote:
>|||Hi Wayne,
The query i sent you works fine in SQL 2000,
by looking at the error msg - i assume that you are trying to run this
in MS-Access. All SQL 2000 queries may not be compatible with MS-Access.
You need to check MS-Access documentation.
Otherwise you can create a view in SQL 2000 and use it in MS-Access
- Sha Anand
"Wayne Wengert" wrote:

> Sha;
> Interesting approch but when I try it I get a "Syntax error (missing
> operator) in expression 'Count(DISTINCT ISNULL(ResidentList.Address2,''))'
> Wayne
> "Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
> news:F235F5F4-BD81-49FA-AFF6-CDD04ED92CC7@.microsoft.com...
>
>

Help Normalizing an Address

While working with a database that contains subcontractors I came
across an interesting scenario with the respect to their addresses.
Currently all fields for the required data are in the one table and is
causing some problems with regards to updating the redundant info
across multiple entries.
Each sub-contractor can have one or multiple addresses. Main Address,
Remit To Address, Correspondance Address, etc...
Simple enough, but...
Some sub-contractors sell their receivables to a factoring company, so
in addition to typical address info, we must also have phone, fax,
contact info, etc., on hand in these situations.
Also, of the sub contractors that use a factoring company, (about 10%)
use the same factoring company. Redundant info.
Also, some sub contractors are a satilte location of a parent company
and althought their phyical address is different, their 'remit to' is
actually their parent co's remit to address.
Main (physical) addresses are important because we link their zip code
to lat/long database to find sub contractors closest to the particular
area of the country where a job needs to be done.
Right now we just have remit to fields in every sub contractor record.
When a Factoring company changes it's address, though, it can be a pain
to update.
Any suggestions?Without knowing how many records you are talking about, I think it would be
easiest to have one address table with a flag column indicating the type of
address and a fk column pointing back to the sub contractor. From an
administration standpoint, this would be easiest.
If you really feel the need to normalize out, then create a table with all
the unique addresses and use their UID as a Fk back to the sub contractor
table. This way when a new address comes in or an existing one is modified,
you can drop the old one and add the new one, while updating subs address
pointers.
"Clyde Venhause" wrote:

> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It seems to be a basic Many-to-Many consideration. At first thought I'd
do it like this:
CREATE TABLE ContractorAddresses (
contractor_id integer not null references Contractors ,
address_id integer not null references Addresses ,
address_type_code integer not null references AddressTypes ,
CONSTRAINT PK_ContractorAddresses
PRIMARY KEY (contractor_id, address_id, address_type_code)
)
The table Address will not have a ContractorID, 'cuz the above table
provides that info. The table AddressTypes will hold data like this:
address_type_code description
-- --
1 Remittance
2 Correspondence
3 Main Office
.. etc. ...
The communication info should be in a different table, much like the
ContractorAddresses table.
CREATE TABLE ContactorContacts (
contractor_id integer not null references Contractors ,
contact_id integer not null references Contacts ,
contact_type_code integer not null references ContactTypes ,
CONSTRAINT PK_ContractorContacts
PRIMARY KEY (contractor_id, contact_id, contact_type_code)
)
The ContactTypes table would have data like this:
contact_type_code description
-- --
1 Purchaser
2 Inspector
3 Lawyer
.. etc. ...
The Contacts table will hold info on who to contact:
CREATE TABLE Contacts (
contact_id integer not null UNIQUE ,
person varchar(30) not null PRIMARY KEY,
.. other columns ? ... -- may change PK requirement
)
A phones table for the contacts:
CREATE TABLE ContactPhones (
contact_id integer not null references Contacts ,
phone_type_code integer not null references PhoneTypes ,
phone_nr char(10) not null,
CONSTRAINT PK_ContactPhones
PRIMARY KEY (contact_id, phone_type_code, phone_nr)
)
The PhoneTypes table would have data like this:
phone_type_code description
-- --
1 Fax
2 Office
3 Purchasing Office
.. etc. ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQmKiW4echKqOuFEgEQKgUACdF8x/K9jakhVTRe6okJI7qtFevNoAn0ky
DEbKy122rQJN1FlCOcaXp9jq
=gg/j
--END PGP SIGNATURE--
Clyde Venhause wrote:
> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>

help needed for fulltext error

hello,
i have been executing the following statement
SELECT title FROM searchtable1 WHERE CONTAINS (filecontent, '"adelaide"')
(which works ok on the development box)
and receiving the following error (on the live site)
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. The specified module could not be
found.
the version is sql-server with sp3 applied...I have had to build the full
text catalogue
by executing stored procedures through asp pages.
has anyone got any ideas on this one?
regards
patrick
Bob,
The OLE DB Provider error message is not enough info to go on. However, I've
seen the message text (The specified module could not be found.) before as
the message text is related to ERROR_MOD_NOT_FOUND. When you installed SQL
Server 2000 or applied a Service Pack were there any reported errors or
problems? Check or post the following files: SearchSetup.log and sqlstp.log
on your problem SQL Server 2000 server. Furthermore, please post the full
output of the following SQL code:
use master
go
SELECT @.@.language
SELECT @.@.version
exec sp_configure 'default full-text language'
go
use <your_database_name>
go
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
go
Regards,
john
"bob" <stirling@.picknowl.com.au> wrote in message
news:u$XEHCtPEHA.252@.TK2MSFTNGP10.phx.gbl...
> hello,
> i have been executing the following statement
> SELECT title FROM searchtable1 WHERE CONTAINS (filecontent, '"adelaide"')
> (which works ok on the development box)
> and receiving the following error (on the live site)
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Execution of a full-text operation failed. The specified module could not
be
> found.
> the version is sql-server with sp3 applied...I have had to build the full
> text catalogue
> by executing stored procedures through asp pages.
> has anyone got any ideas on this one?
> regards
> patrick
>
>
|||thanks john
the hosting providers are now having a look from their end so i will post
the data in a few days if they cant fix it
bob
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uqNYiKtPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Bob,
> The OLE DB Provider error message is not enough info to go on. However,
I've
> seen the message text (The specified module could not be found.) before as
> the message text is related to ERROR_MOD_NOT_FOUND. When you installed
SQL
> Server 2000 or applied a Service Pack were there any reported errors or
> problems? Check or post the following files: SearchSetup.log and
sqlstp.log[vbcol=seagreen]
> on your problem SQL Server 2000 server. Furthermore, please post the full
> output of the following SQL code:
> use master
> go
> SELECT @.@.language
> SELECT @.@.version
> exec sp_configure 'default full-text language'
> go
> use <your_database_name>
> go
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> go
> Regards,
> john
>
> "bob" <stirling@.picknowl.com.au> wrote in message
> news:u$XEHCtPEHA.252@.TK2MSFTNGP10.phx.gbl...
'"adelaide"')[vbcol=seagreen]
not[vbcol=seagreen]
> be
full
>

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

help needed ! sql query

my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)
it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)
Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button
or user can fill 2 parameter and hit enter
so on the other hand (2nd page) how should i query the database accordingly ...

Example:
if (myTextBox.Text == String.Empty)
{
myParameter.Value = DBNull.Value;
}
else
{
myParameter.Value = myTextBox.Text;
}
|||

You can always use an If Else loop to write the query.
If IsDbNull(parameter) Then
parameter = (Whatever default value or just null)
Else
parameter = Request.Form("name of parameter")
End If
I hope this is clear enough.

Help Me Write This Query

Sorry for my newbieness:
I have a table (Cases) where Case_Number is the unique id field.
Another table (CommentsTable) contains these fields
Case_Number
Comment_Date
Comment
As users enter comments to the database, the comment gets a date/time
stamp (Comment_Date) and is recorded in the Comment field, and the case
number for that comment is also recorded.
I want my query to return fields from the Cases table, but also return
the last comment from the CommentsTable. I know I need to use the
Case_Number and MAX(Comment_Date) then return the Comment for that max
date and that case number, and I'm pretty sure this will be a sub
query, I just can't figure out how to put it all together.
Could somebody get me going in the right direction?
TIA,
todTod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
>
select * from CommentsTable ct where not exists(select 1 from
CommentsTable ct1
where ct.Case_Number = ct1.Case_Number and ct.Comment_Date <
ct1.Comment_Date)|||Tod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
> As users enter comments to the database, the comment gets a date/time
> stamp (Comment_Date) and is recorded in the Comment field, and the case
> number for that comment is also recorded.
> I want my query to return fields from the Cases table, but also return
> the last comment from the CommentsTable. I know I need to use the
> Case_Number and MAX(Comment_Date) then return the Comment for that max
> date and that case number, and I'm pretty sure this will be a sub
> query, I just can't figure out how to put it all together.
> Could somebody get me going in the right direction?
> TIA,
> tod
Untested:
SELECT C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases AS C
JOIN dbo.CommentsTable AS T
ON C.Case_Number = T.Case_Number
WHERE T.Comment_Date =
(SELECT MAX(Comment_Date)
FROM dbo.CommentsTable
WHERE Case_Number = T.Case_Number);
Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
could still get more than one row per Case_Number.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This might be neater if you are returning a single case. The Case_Number is
stored in the @.CaseNumber variable in this example:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_Date DESC
If you wish to remove time adjustments from the equation (e.g. daylight
saving adjustments) then you could add an IDENTITY column to the
CommentsTable table (Comment_ID) and use the following:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_ID DESC
Chris
"David Portas" wrote:

> Tod wrote:
> Untested:
> SELECT C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases AS C
> JOIN dbo.CommentsTable AS T
> ON C.Case_Number = T.Case_Number
> WHERE T.Comment_Date =
> (SELECT MAX(Comment_Date)
> FROM dbo.CommentsTable
> WHERE Case_Number = T.Case_Number);
> Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
> could still get more than one row per Case_Number.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanx. I think this is closest to what I need.
Chris Howarth wrote:
> This might be neater if you are returning a single case. The Case_Number i
s
> stored in the @.CaseNumber variable in this example:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_Date DESC
>
> If you wish to remove time adjustments from the equation (e.g. daylight
> saving adjustments) then you could add an IDENTITY column to the
> CommentsTable table (Comment_ID) and use the following:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_ID DESC
> Chris
>
> "David Portas" wrote:
>