combining two tables with a full-text search

I had a table that was terribly in need of normalisation that I have now
split into two tables. The table contains three "similar" fields that I
were previously indexed using a full-text query. I now need to do the query
twice as subqueries, UNION the results, then order by the calculated rank.
Problem is - the rank appears to be independent between the two tables, to
the results are coming up with one query always being higher than the other.
Is there any way to pre-select or cap the rank value, or another way to
search these two tables so they're more "combined"?
Thanks in advance,
Duncan
Probably not as the rank is generated on a per table basis.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <dunc@.ntpcl.f9.co.uk> wrote in message
news:Ouh0hapaFHA.2496@.TK2MSFTNGP14.phx.gbl...
> I had a table that was terribly in need of normalisation that I have now
> split into two tables. The table contains three "similar" fields that I
> were previously indexed using a full-text query. I now need to do the
query
> twice as subqueries, UNION the results, then order by the calculated rank.
> Problem is - the rank appears to be independent between the two tables, to
> the results are coming up with one query always being higher than the
other.
> Is there any way to pre-select or cap the rank value, or another way to
> search these two tables so they're more "combined"?
> Thanks in advance,
> Duncan
>
|||One solution, that wouldn't be the best but would work, would be to
create a third table containing all the fields you wish to index with
the primary key associated with it, then index that table instead.

Combining two tables to make a third

Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||This is a multi-part message in MIME format.
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns =from each table instead of using [*],
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<mngong@.gmail.com> wrote in message =news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> > Your help is appreciated have been on this
>
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Based on this limited =information:
INSERT INTO Table3
SELECT =*
FROM =Table1
=CROSS JOIN Table2
If you must control the field order, =you may need to list the columns from each table instead of using [*],
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...> =Need help combining two tables into a third with corresponding fields> of =both tables> table 1> time 12 mike work> =time 13 john sleep> times 24 George jump> table 2> 23 sam run> There is a table3 which has all the columns of the two =tables but i> cannot seem to come around to combine them> time =12 mike work 23 sam run> time 13 john sleep 23 =sam run> times 24 George jump 23 sam run> > Your help =is appreciated have been on this>

---

--=_NextPart_000_0C6D_01C6B148.4585B4F0--

Combining two Tables into one TempTable, with a condition.

Hi,

I'm very new to Sql, and need to combine information from two tables into one temp table. All tables have the same structure.

The problem is I want to set a condition that if Field 1 is already in the TempTable, dont include that field from the second table. Along the lines of

Table1 Description = Blue, if Table 2 Description = Blue, dont include this row.

I have been trying to use INSERT..SELECT and UNION. , the only problem I have is that I cannot come up with a working conditional statement to prevent both complete sets of data being written to the TempTable.

I'm using Sql Server 2005 deveoper Edition and VS2005Pro.

Any ideas on how to get around this would be appreciated.

Tailor

Hi Tailor,

I'm not 100% clear on what your problem is. Is it that you wanted to union two tables and insert this result into a temp table only if data for a particular column (pk I presume) doesn't already exist in the temp table, or is it that you want to union the result of two tables but only select (and thus insert) distinct rows?

Can you provide a bit more info?

Cheers

Rob

|||

Hi Rob,

Thanks for your reply, I'll try to make this a little clearer with some code.

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

UNION

SELECT Description,Id,Reference

FROM Table2

WHERE (Id = 'STR001') AND Description != Table1.Description.

Without the AND statement, all data from both tables is inserted in tempTable, however with the And statement, I get the error message.

'The multi-part identifier Table1.Description could not be bound'

If the description from Table2, matches the Description in Table1, I dont want to include the row in the TempTable.

Sorry if i'm not too clear. At 64, trying to learn VS2005, Sql2005, and intergrating excel in my code, and write a reasonable sized application, all in six months is not something I can recommend. There is just so much to learn, and I dont have a good grasp of a lot of the basics.

If you can suggest a workable solution to my problem, it would be much appreciated.

John

|||

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

From Table1

Where (Id = 'STR001')

INSERT INTO tempTable (Description,Id,Reference)

SELECT Description,Id,Reference

FROM Table2 t left outer join temptable tt on t.description = tt.description

WHERE (t.Id = 'STR001') AND tt.description is null

Regards

|||

This is a bit of a messy solution

either

1. Insert the data from both tables with a UNION, but place the UNION statement into a subquery and alias the columns to be inserted.

2. Insert data from table1. Then do the same insert from table2 where NOT EXISTS in the temp table (or table1, either will work). This is a common task you will need to learn to do in SQL Server

|||

Hi JMattias and SHughes,

Thank you both, very much, for your replies. It not only solved the problem, I learnt a lot more on the way through.

Your assistance is greatly appreciated.

John

sqlsql

Combining two tables from different databases

I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.

To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.

To better illustrate:

database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58

The overlapping cusno's are not the same customer.

How can I get all the cusno from the arcus file in database two to the arcus file in database one?

Is this even possible?not without creating all new PK values.

if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)

in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.

the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.

DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.

Combining two table into a single table

I have two tables with a 1 to many relationship:

tblCarCompanies
ID Company
1 Mazda
2 Nissan

tblCarModels
ID Company_fk Model
1 1 Miata
2 1 Mazda3
3 2 Sentra
4 2 Pathfinder
5 2 Maxima

What's the best way to query these two table into one result set like:
tblCars
Company Models
Mazda Miata, Mazda3
Nissan Sentra, Pathfinder, Maxima

Maybe something like this:

declare @.carCompany table
( ID integer,
Company varchar(10)
)
insert into @.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'

declare @.carModel table
( ID integer,
Company_fk integer,
Model varchar(12)
)
insert into @.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'

select company,
reverse(substring(reverse(
( select model + ', ' as [text()]
from @.carModel b
where a.id = b.company_Fk
order by model
for xml path('')
)), 3, 200)) as Models
from @.carCompany a

/*
company Models
- --
Mazda Mazda3, Miata
Nissan Maxima, Pathfinder, Sentra
*/

|||One thing I noticed is that if my Models field contains an "&" it will return "&amp;".
I'm guessing it has to do with the for xml path('') conversion. I can simply do a replace(myOutput,'&amp;','&') but I'm not sure if it'll affect any other characters.

What exactly does the for xml path do and is there a way to convert it back without the replace?
|||Yes, I have seen aberations before because of the path(''); you definitely need to look out for it and you might even need to choose a different solution if it becomes a significant problem. Another alternative is to use a function -- preferably an inline function -- in conjunction with the CROSS APPLY operator. Would you like to see an example of such an alternative?|||Sure, an example would be great. Thanks!
|||

I didn't come up with a good way to create an INLINE function for this. Maybe somebody else sees a straight-forward way to do this. I mocked up this test with these tables:

create table dbo.carCompany
( ID integer,
Company varchar(10)
)
go
insert into dbo.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'
go

create table dbo.carModel
( ID integer,
Company_fk integer,
Model varchar(12)
)
go
insert into dbo.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'
go

An example of a scalar function is like this:

alter function dbo.listModels
( @.prm_companyID integer
)
returns varchar(300)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return @.modelList

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

set @.modelList = reverse(substring(reverse(@.modelList), 3, 300))

return @.modelList

end

go

select id,
dbo.listModels (id) as Models
from carCompany

/*
id Models
--
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

An example with a table function and cross apply is like:

alter function dbo.companyModels
( @.prm_companyID integer
)
returns @.companyModels table
( modelList varchar(300)
)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

insert into @.companyModels
select reverse(substring(reverse(@.modelList), 3, 300))

return

end

go

select id,
m.modelList as Models
from carCompany
cross apply dbo.companyModels (id) m

/*
id Models
-- --
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

There are a couple of additional things to note:

It is critical to these functions that you have an index on the MODEL table based on COMPANY_FK; otherwise, you will table scan You might be able to get away with a NOLOCK optimizer hint in these functions; if you are not sure, do NOT add the NOLOCK hint.

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure?
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.com
Aditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables
sqlsql

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatena
tion, or expose a
computed column in the table. That way the data doesn't get out of sync in c
ase someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How wil
l
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null space
s
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concate
nation, or expose a
> computed column in the table. That way the data doesn't get out of sync in
case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL i
n either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to
go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:
> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How
> > will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:
> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>> Assuming no nulls in firstname or lastname column:
>> UPDATE tbl
>> SET FULLNAME = FirstName + ' ' + LastName
>> But, why do you need to store this? You can create a view with the concatenation, or expose a
>> computed column in the table. That way the data doesn't get out of sync in case someone modifies
>> the
>> first name or last name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
>> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
>> > LASTNAME, ADDRESS, TELEPHONE,...
>> > Now i have added a new column in the table, naming it FULLNAME. In this i
>> > want to insert the FIRSTNAME and the LASTNAME
>> > combined with a space in between, the rest all remaining the same. How will
>> > i do it?Is it possible with a Stored Procedure'
>> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
>> >
>> > You can also email me at : aditya595@.yahoo.com
>>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining two fields into one

I have a small problem.
In my query I am calling a field of status and a field of statusyearcount. I
need to get these into one cell in a table, called statusyearcount.
ie. Status = C
StatusYearCount = 10
Need to show C10 in the table.
I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
getting an "incorrect format string".
Im gathering it because one is an int and the other varchar. Can somebody
please point me in the right direction here.
Thanks muchly.You can cast the Field.
=Fields!Status.Value + Int(Fields!StatusYearCount.Value)
--
| Thread-Topic: Combining two fields into one
| thread-index: AcWo+8ArbHZHrGD+TI2fppOvcbIgrg==| X-WBNR-Posting-Host: 202.175.143.143
| From: "=?Utf-8?B?TmF0IEpvaG5zb24=?="
<NatJohnson@.discussions.microsoft.com>
| Subject: Combining two fields into one
| Date: Wed, 24 Aug 2005 15:32:46 -0700
| Lines: 18
| Message-ID: <3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51089
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a small problem.
|
| In my query I am calling a field of status and a field of
statusyearcount. I
| need to get these into one cell in a table, called statusyearcount.
|
| ie. Status = C
| StatusYearCount = 10
|
| Need to show C10 in the table.
|
| I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I
keep
| getting an "incorrect format string".
|
| Im gathering it because one is an int and the other varchar. Can
somebody
| please point me in the right direction here.
|
| Thanks muchly.
|
||||This will do it:
=Fields!Status.Value & CStr(Fields!StatusYearCount.Value)
CStr converts integer to string, allowing succesful concatenation.
GeoSynch
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com...
>I have a small problem.
> In my query I am calling a field of status and a field of statusyearcount. I
> need to get these into one cell in a table, called statusyearcount.
> ie. Status = C
> StatusYearCount = 10
> Need to show C10 in the table.
> I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
> getting an "incorrect format string".
> Im gathering it because one is an int and the other varchar. Can somebody
> please point me in the right direction here.
> Thanks muchly.
>

Combining the content of two tables

Hi all,

How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks

Table A

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Table B

Client Weight Purchase

Jim 2 5

Lee 4 3

Bob 6 7

Combination table (result)

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Jim 2 5

Lee 4 3

Bob 6 7

Give a look to the UNION and UNION ALL operators in books online. It should look something like this:

Code Snippet

select client,
weight,
purchase
from [table a]
union all -- or perhaps union
select client,
weight,
purchase
from [table b]

|||Works perfectly. Thanks.sqlsql

Combining tables

Here's an issue I am having, I am currently getting data from a customer table, sales table, and a definitions table, it has 2 inner joins, It creates a temporary output table. Now, this table only includes data from the customers that actually had sales. I need a way to select into that temp table all the customers that are not currently in that table. Is there a way to do this? Any help would be greatly appreciated.

Jim

Hi,

therefore better use a LEFT JOIN which will be also applied to those customers which don′t have any entries in the joined tables. But this is just a common approach which *could* fit to your needs. Before giving you more information or advices please post the query you are using here.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I have tried a left join with no avalial, Actually I have tried every join I could think of. Basically the stored procedure I am using to create the table uses 4 join statements to evaluate a number of sales totals, it creates a temp table, but no matter how may times I evaluate the statement or at what point, I cannot seem to get the left join to act any differently than an inner or right.

Included in the stored procedure I am using, perhaps someone out there will see something I do not.

ALTER PROCEDURE [dbo].[sp_get_minimumData] (@.StartDate as DateTime, @.StopDate as DateTime, @.prdGroup as char(1))

AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_minimum_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tmp_minimum_data]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Current_Minimums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tmp_Current_Minimums]

SELECT dbo.CustomerBase.qb_seq AS qb_seq, dbo.CustomerBase.CustQbName AS Customer,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_1_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_1) ELSE '0.00' END AS SalesItem1,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_2_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_2) ELSE '0.00' END AS SalesItem2,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_3_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_3) ELSE '0.00' END AS SalesItem3,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_4_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_4) ELSE '0.00' END AS SalesItem4,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_5_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_5) ELSE '0.00' END AS SalesItem5,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_6_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_6) ELSE '0.00' END AS SalesItem6,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_7_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_7) ELSE '0.00' END AS SalesItem7,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_8_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_8) ELSE '0.00' END AS SalesItem8,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_9_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_9) ELSE '0.00' END AS SalesItem9,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_10_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_10) ELSE '0.00' END AS SalesItem10,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_11_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_11) ELSE '0.00' END AS SalesItem11,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_12_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_12) ELSE '0.00' END AS SalesItem12,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_13_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_13) ELSE '0.00' END AS SalesItem13,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_14_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_14) ELSE '0.00' END AS SalesItem14,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_15_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_15) ELSE '0.00' END AS SalesItem15,

CASE WHEN dbo.tbl_ItemizerMap.sales_item_16_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_16) ELSE '0.00' END AS SalesItem16,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_1_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_1) ELSE '0.00' END AS TaxItem1,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_2_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_2) ELSE '0.00' END AS TaxItem2,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_3_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_3) ELSE '0.00' END AS TaxItem3,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_4_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_4) ELSE '0.00' END AS TaxItem4,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_5_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_5) ELSE '0.00' END AS TaxItem5,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_6_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_6) ELSE '0.00' END AS TaxItem6,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_7_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_7) ELSE '0.00' END AS TaxItem7,

CASE WHEN dbo.tbl_ItemizerMap.tax_item_8_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_8) ELSE '0.00' END AS TaxItem8,

CASE WHEN dbo.tbl_ItemizerMap.service_charge_min = 'True' THEN SUM(dbo.tbl_BatchArchive.service_charge) ELSE '0.00' END AS ServiceChargeItem,

CASE WHEN dbo.tbl_ItemizerMap.auto_service_charge_min = 'True' THEN SUM(dbo.tbl_BatchArchive.auto_service_charge) ELSE '0.00' END AS AutoServiceChargeItem,

dbo.CustomerBase.minPeriodGroup

INTO tmp_minimum_data

FROM dbo.tbl_BatchArchive INNER JOIN

dbo.tbl_ItemizerMap ON dbo.tbl_BatchArchive.rvc_num = dbo.tbl_ItemizerMap.rvc_num AND

dbo.tbl_BatchArchive.qb_FullName <> dbo.tbl_ItemizerMap.cash_account_name INNER JOIN

dbo.CustomerBase ON dbo.tbl_BatchArchive.qb_seq = dbo.CustomerBase.qb_seq

WHERE (dbo.tbl_BatchArchive.bus_date BETWEEN @.StartDate AND @.StopDate) AND (dbo.CustomerBase.minPeriodGroup = @.prdGroup)

GROUP BY dbo.tbl_BatchArchive.qb_FullName, dbo.tbl_BatchArchive.bus_date, dbo.tbl_ItemizerMap.sales_item_1_min, dbo.tbl_ItemizerMap.sales_item_2_min,

dbo.tbl_ItemizerMap.sales_item_3_min, dbo.tbl_ItemizerMap.sales_item_4_min, dbo.tbl_ItemizerMap.sales_item_5_min,

dbo.tbl_ItemizerMap.sales_item_6_min, dbo.tbl_ItemizerMap.sales_item_7_min, dbo.tbl_ItemizerMap.sales_item_8_min,

dbo.tbl_ItemizerMap.sales_item_9_min, dbo.tbl_ItemizerMap.sales_item_10_min, dbo.tbl_ItemizerMap.sales_item_11_min,

dbo.tbl_ItemizerMap.sales_item_12_min, dbo.tbl_ItemizerMap.sales_item_13_min, dbo.tbl_ItemizerMap.sales_item_14_min,

dbo.tbl_ItemizerMap.sales_item_15_min, dbo.tbl_ItemizerMap.sales_item_16_min, dbo.tbl_ItemizerMap.tax_item_1_min,

dbo.tbl_ItemizerMap.tax_item_2_min, dbo.tbl_ItemizerMap.tax_item_3_min, dbo.tbl_ItemizerMap.tax_item_4_min,

dbo.tbl_ItemizerMap.tax_item_5_min, dbo.tbl_ItemizerMap.tax_item_6_min, dbo.tbl_ItemizerMap.tax_item_7_min,

dbo.tbl_ItemizerMap.tax_item_8_min, dbo.tbl_ItemizerMap.service_charge_min, dbo.tbl_ItemizerMap.auto_service_charge_min,

dbo.CustomerBase.minPeriodGroup, dbo.CustomerBase.CustQbName, dbo.CustomerBase.qb_seq

SELECT dbo.CustomerBase.qb_seq, dbo.CustomerBase.CustomerAccount, dbo.tmp_minimum_data.Customer,

SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)

+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)

+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)

+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)

+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)

+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)

+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)

+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)

+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem) AS ValueTwardMinimum, dbo.CustomerBase.minValue,

CASE WHEN dbo.CustomerBase.minValue - (SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)

+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)

+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)

+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)

+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)

+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)

+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)

+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)

+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem))> 0 THEN

dbo.CustomerBase.minValue - (SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)

+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)

+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)

+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)

+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)

+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)

+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)

+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)

+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem)) ELSE 0 END AS InvoiceAmmount

INTO tmp_Current_Minimums

FROM dbo.tmp_minimum_data INNER JOIN

dbo.CustomerBase ON dbo.CustomerBase.qb_seq = dbo.tmp_minimum_data.qb_seq

WHERE (dbo.tmp_minimum_data.SalesItem1 + dbo.tmp_minimum_data.SalesItem2 + dbo.tmp_minimum_data.SalesItem3 + dbo.tmp_minimum_data.SalesItem4

+ dbo.tmp_minimum_data.SalesItem5 + dbo.tmp_minimum_data.SalesItem6 + dbo.tmp_minimum_data.SalesItem7 + dbo.tmp_minimum_data.SalesItem8

+ dbo.tmp_minimum_data.SalesItem9 + dbo.tmp_minimum_data.SalesItem10 + dbo.tmp_minimum_data.SalesItem11 + dbo.tmp_minimum_data.SalesItem12

+ dbo.tmp_minimum_data.SalesItem13 + dbo.tmp_minimum_data.SalesItem14 + dbo.tmp_minimum_data.SalesItem15 + dbo.tmp_minimum_data.SalesItem16

+ dbo.tmp_minimum_data.TaxItem1 + dbo.tmp_minimum_data.TaxItem2 + dbo.tmp_minimum_data.TaxItem3 + dbo.tmp_minimum_data.TaxItem4 + dbo.tmp_minimum_data.TaxItem5

+ dbo.tmp_minimum_data.TaxItem6 + dbo.tmp_minimum_data.TaxItem7 + dbo.tmp_minimum_data.TaxItem8 + dbo.tmp_minimum_data.ServiceChargeItem + dbo.tmp_minimum_data.AutoServiceChargeItem <> 0) AND

(dbo.CustomerBase.minValue - (dbo.tmp_minimum_data.SalesItem1 + dbo.tmp_minimum_data.SalesItem2 + dbo.tmp_minimum_data.SalesItem3 + dbo.tmp_minimum_data.SalesItem4

+ dbo.tmp_minimum_data.SalesItem5 + dbo.tmp_minimum_data.SalesItem6 + dbo.tmp_minimum_data.SalesItem7 + dbo.tmp_minimum_data.SalesItem8

+ dbo.tmp_minimum_data.SalesItem9 + dbo.tmp_minimum_data.SalesItem10 + dbo.tmp_minimum_data.SalesItem11 + dbo.tmp_minimum_data.SalesItem12

+ dbo.tmp_minimum_data.SalesItem13 + dbo.tmp_minimum_data.SalesItem14 + dbo.tmp_minimum_data.SalesItem15 + dbo.tmp_minimum_data.SalesItem16

+ dbo.tmp_minimum_data.TaxItem1 + dbo.tmp_minimum_data.TaxItem2 + dbo.tmp_minimum_data.TaxItem3 + dbo.tmp_minimum_data.TaxItem4 + dbo.tmp_minimum_data.TaxItem5

+ dbo.tmp_minimum_data.TaxItem6 + dbo.tmp_minimum_data.TaxItem7 + dbo.tmp_minimum_data.TaxItem8 + dbo.tmp_minimum_data.ServiceChargeItem + dbo.tmp_minimum_data.AutoServiceChargeItem)

<> 0) AND (dbo.CustomerBase.minValue <> 0)

GROUP BY dbo.tmp_minimum_data.Customer, dbo.CustomerBase.minValue, dbo.CustomerBase.CustomerAccount, dbo.CustomerBase.qb_seq

DROP TABLE tmp_minimum_data

ALTER TABLE dbo.tmp_Current_Minimums ADD

changed Boolean NULL,

reason nvarchar(50) NULL

SELECT * FROM tmp_Current_Minimums

|||

Hi

I think LEFT and INNER joins behave the same in 2 cases:

1. No data is missing from the left joined table

2. You are using the left joined table in the WHERE clause. In this case , try to add your criteria to the LEFT JOIN ON criteria.

NB

|||Join to the customer using a NOT EXISTS (in the temp table). That will pull back all the rows from customer not already in the temp table.|||I would like to thank everyone, I ended up using a Cursor and some creative data manipulation to do what I needed, but thank you all for your assistance.

Combining Table and Matrix format in one Report in RS2005

Hello,

I am using RS 2005 trying to create the following report. My report consists of the following columns: Question, Sub Question, N as Number of Responses, All as Average for all responses per given question and sub question, and Ethnicity column which is presented here in a Matrix format with ethnic group as columns and average response as Data values. It looks like my challenge is to combine Matrix format report (Ethnicity column) with a data such as N and All columns which are more like a table format. Any input how I could tackle this is greatly appreciated.

Thank you!

--

1.How often have you done each of the following?

N All F M Asian Multi-cultural a. Worked on a paper or project that required integrating ideas or information from various sources 1134 3.96 3.95 3.99 3.54 4.50 b. Used library resources 1132 4.21 4.26 4.09 4.12 4.33 c. Prepared multiple drafts of a paper or assignment before turning it in 1130 3.90 3.97 3.76 3.80 4.50

-How the source data looks like?sqlsql

Combining subquery results into one field

Hello there

I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:

bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

thanks

I would look at returning two results to a dataset then creating a relationship between them. Displaying the results is pretty easy using nested repeaters:http://gridviewguy.com/ArticleDetails.aspx?articleID=185

Combining rows in a table(again)

I've seen a number of questions on combining rows, but not one
exactly like this. I have a solution, but I'd like to know
if there are other ways.
I'd like to select and combine rows from a table. Here's a simplified
version of the table:
tab1
key date status
1 1/1/06 stat1
1 1/2/06 stat2
1 1/3/06 stat3
1 1/4/06 stat4
2 1/1/06 stat1
2 1/2/06 stat2

And the desired results:
key date status prevstatus
1 1/1/06 stat1 null
1 1/2/06 stat2 stat1
1 1/3/06 stat3 stat2
1 1/4/06 stat4 stat3
2 1/1/06 stat1 null
2 1/2/06 stat2 stat1

Here's the simplified version of the solution:
select
a.*,b.status prevstatus
from
tab1 a
left join
tab1 b
on a.key = b.key and
b.date =
(select max(date) from tab1 c
where
a.key = c.key and
a.date > c.date
)

Is there a better way?Your resultset doesn't make much sense. Can you explain it?|||Your resultset doesn't make much sense. Can you explain it?It's a "PeopleSoft" join.

No gams, I'm pretty sure that is optimal for the case you've presented.

-PatP|||Yes.
The idea is to get a row and the most recent previous status. The first row in a set will have no previous status.|||It's a "PeopleSoft" join.

No gams, I'm pretty sure that is optimal for the case you've presented.

-PatP
Does that make me a "PeopleSoft" joiner? What is a "PeopleSoft" join?|||The "PeopleSoft join" was a reference for Brett's information. Brett is quite familiar with the glories of PeopleSoft.

PeopleSoft is an ERP package. The PeopleSoft packages use a data representation that often needs to reference the "prior" row based on a presumed sequence.

-PatP

Combining Row Data

I am adding data to a table from a table of identified duplicates.
These duplicates are not Key Violations, they are actual double entries
where one was deactivated prior to the new record being added (not very
clean but necessary given the system the data originates from). What I
would like to do is to take the data and combine some fields of the
duplicate rows.
I have created my mapping table (the table I want to add data to) which
looks like:
UniversalID (Identity)(PK)
Original_ID1 <-key from 1st row
Original_ID2 <-key from 2nd row
2ndSystemID
My data would look like this:
OriginalID Name 2ndSystemID
123 Smith, John KHGK39
124 Smith, John KHGK39
..
What I want is
UniversalID Original_ID1 Original_ID2 2ndSystemID
1 123 124 KHGK39
...
I know that this is doable, and I also know that I should know how, but
just can't seem to see the forest because all of the trees are in my
way.
Thanks for any help you can provide.
MTAnd if there are triplicates?
Don't try fixing old flaws by introducing new ones - such as breaking normal
form. I'd sugesst either deleting unwanted rows or adding a table to store
duplicate values of OriginalID referencing a proper primary key (e.g
2ndSystemID). Normalize now and prevent further difficulties.
ML
http://milambda.blogspot.com/|||Not really trying to break normal form. I need all of these values to
reference back to the original systems where the data lives. This is
why I need all of them. The UniversalID will be the new Key, the old
IDs, will become simple references.
I truly wish I could use only one, but unfortunately due to the nature
of the data I am dealing with, I am unable to and must find a work
around.
MT|||MT wrote:
> Not really trying to break normal form. I need all of these values to
> reference back to the original systems where the data lives. This is
> why I need all of them. The UniversalID will be the new Key, the old
> IDs, will become simple references.
> I truly wish I could use only one, but unfortunately due to the nature
> of the data I am dealing with, I am unable to and must find a work
> around.
> MT
Won't your proposal fail if there are more than 2 original ids? How
many do you expect to have to cope with?
Unfortunately you haven't given much information about keys. Here's a
guess at what I'd do:
CREATE TABLE users (universalid INTEGER PRIMARY KEY, name VARCHAR(50)
NOT NULL UNIQUE, systemid INTEGER NOT NULL);
CREATE TABLE original_ids (original_id INTEGER NOT NULL PRIMARY KEY,
universalid INTEGER NOT NULL REFERENCES users (universalid));
INSERT INTO users VALUES (1, 'Smith, John', 'KHGK39');
INSERT INTO orginal_ids VALUES (123,1);
INSERT INTO orginal_ids VALUES (124,1);
A join will map any number of original ids to the universal one.
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
--

Combining results in one string

I have a table as follows:
SizeID | Description
1 Extra Small
2 Small
3 Medium
4 Large
5 Extra Large
And then a Product_Size relationship table:
ProductID | SizeID
1000 1
1001 3
1001 4
1001 5
1010 2
1010 3
I want to be able to write a query which returns the size descriptions in
one single string, separated by commas.
For example, if I request for Product #1001, I want the return string to be:
'Medium, Large, Extra Large'
Please show me how this query can be written.
Thanks!You can do this by creating function.
CREATE FUNCTION dbo.GetSize(@.ProductId int)
RETURNS varchar(50)
AS
Begin
Declare @.ReturnValue Varchar(500), @.descripton Varchar(50)
Set @.ReturnValue = ''
Declare ProductSize Cursor
For Select Description
From Product_Size, SizeMaster
Where Product_Size.SizeID = SizeMaster .SizeID
AND ProductId = @.ProductId
Open ProductSize
Fetch Next From ProductSize into @.descripton
While @.@.Fetch_status = 0
Begin
SET @.ReturnValue = @.ReturnValue + @.descripton + ','
Fetch Next From ProductSize into @.descripton
End
Set @.ReturnValue = left(@.ReturnValue,Len(@.ReturnValue)-1)
Close ProductSize
Deallocate ProductSize
Return(@.ReturnValue)
End
Select Distinct ProductId, dbo.GetSize(productID) from Product_Size
Thanks
Baiju
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>|||I made a simillar request yesterday. check the post titled "Concating Values
of a Column based on a Group"
Gopi
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
>I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
> be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>

Combining results in Comma delimitered strings

I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?DECLARE @.commadelimitedthisisanannoyingstringname VARCHAR(8000)

SELECT @.thatstringupthereyouregoingtonottypethis = ''

SELECT @.String = PersonId + ', ' + @.String
FROM Person

SELECT LEFT(@.String, LEN(@.String)-1)

or something like that|||Thanks, that seems to work,... I didn't think it would but some how it does...|||Thanks, that seems to work,... I didn't think it would but some how it does...

It should have worked. :) Why would you not think it would, just curious.|||I would have thought that for that structure to work there would have had to be some sort of loop or something...

I thought I had seen it done using some other function like coalesce or something but when I read the help it didn't look right...

Hmmm,... actually thinking about it now it makes sense... basically it ignores what if selected in the table select and just selects the final built up string...

Seems a little inefficient, is there a better way??|||Actually, if you do a search here or on www.sqlteam.com, you'll find all kinds of ways to do it. :) You can do it with a function using COALESCE. I think for a single string though, this one is pretty efficient.|||Come on, derrick, NOBODY here thought that would work the first time we saw it months back! It's probably one of the most popular tricks on the forum!|||?? You're joking right? I have scripts going way back using that.|||I know that folks use the living stuff out of this construct, but it shouldn't work. It actually violates the SQL-92 standard, since the column value is derived iteratively. My guess is that once the standards committee realizes that this flaw exists in a widely used SQL dialect, they'll add a test for it to the test suite and shortly thereafter Microsoft will either eliminate the behavior or make it switch dependant.

-PatP|||Yup, it's a nifty little thing that has gotchya's people don't think about, and as always tend to overuse it. Like with everything else that is non-standard, when it comes to an end, a lot of these guys are gonna be screaming bloody hell, but it's only because they were lazy at the beginning, though not lazy enough to prevent it right there, and have a beer ;)|||Yeah, but it is lots of fun to watch them running around, screaming to the four winds when the database engine behavior is fixed and their code breaks. As you are fond of observing, those who ignore history are doomed to repeat it!

-PatP|||Okie then guys,... what is the right way to do it? To be honest I have actually implemented a different solution as I didn't get a reply in time so it isn't going to cause me any problems but I would be interested in knowing if there is a proper way or not...|||The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||Well that answer gains zero points for usefullness...

No offense meant. For the purposes of the project I am doing I need a comma delimited string of all the id's. This is not for displaying but for internal processing. A string may not be the best option but at this point it time is the most flexible and the project at hand. The truth is that there is no front in for the problem I am working on. It's a logic problem that I want to solve. Once it is solved I might decide that it is no use or I might decide that there is a front end requirement but at this point there is not.

Now while I agree that I probably don't want SQL to return a comma delimited string because it is a misrepresentation of the actual data I do want to know what is the best way of getting sql to return a comma delimited string so I know what I am talking about when I rule it out as an option.|||No matter how the string is getting formed, it's still limited to the total length of 8000 character (wonder how many times I tried to hint it?:rolleyes: )

Why don't you tell us what the app is for, and why it needs a comma-delimited listing of PersonIDs? Someone (maybe me) would be able to come up with an alternative, hey?! ;)|||I think the answer to your hint question is once (in this post anyway).

The string is used for determining individual branches in a family tree. The processing is all done using asp/vbscript. An array might be better then a string but because you are shuffling data into and out of other stings/arrays at this point a string is the best contruct to test the theory and determine it's usefulness.

At this point conversion of a recordset to a string is simplier then a recordset to an array.

The actually processing overhead of using a string is probably higher but until I determine the usefulness of the function their is little point moving to an array which would be more complex at this point.

Before you ask, yes, you are stuck with asp and vbscript. It can't go to a vb component and it can't upgrade to asp.Net.

Knock yourself out.|||Hey-hey-hey, with that kind of attitude, YOU knock yourself out, not me, alright?! I was just trying to help you (which is why I choose to post here), but as you MIGHT have noticed, it's NOT my problem. If you think you're better than that, - KNOCK YOURSELF OUT, do yourself a favor.

EDITED: and BTW, I hinted on VARCHAR limitation at least twice. I simply didn't think that such an obvious thing as this needs to be mentioned...Obviously it does, hey?!|||*sigh* why is it that people are easily offended...

Look, I appreciate what you are saying and I appreciate your input, the "knock yourself out" line is a standard line from where I come from to say "go for it and good luck". I'm sorry it offended you.|||Fair dinkum. :cool:|||WHAT WAS WRONG WITH THIS ANSWER? Tel me straight, please, I I promise I will take a serious consideration over how I post my answers...The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||What's wrong with the answer is it doesn't actually answer the question.

The question is, is there a proper way to create a comma delimited string in the manner described within SQL (and if there is, what is it).

It is not, how should I build my application so that I do not have to use a comma delimited string, nor is it what restrictions will I put in place by using a comma delimited string.

I'd be quite happy if some one answered "there is no 'proper' way to generate a comma delimited string from sql". I am aware that there are going to be limitations, but in some cases (not this one) the limitation may not come into play (depending on data and data structures).|||Hey, have it your way. I hope the the limitation pointed out is not gonna be a problem for you ;)|||Eventually it would be, but since this was only to test a theory it's not going to and like I said previously I have actually gone about it a different way, but it would still be interesting to know if there is a "proper" way to do it...

An example of where it might be useful is when you want a comma delimited list of the months that are stored in a particular table. You can be certain that the length of the string will not exceed 8000 characters...

I'm not sure where else it might be useful but there are bound to be others.

Thanks for your input.sqlsql

Combining results from 2 databases in 1 table

Hi, I hope someone will take a moment to answer my question.
I'm combining results from 2 databases. The SQL statement works fine in
Query Analyser using the full names of the database tables (db1.dbo.table1,
db2.dbo.table1). In SRS the dataset executes correctly under the data tab
when using the master table as the shared datasource.
However in the layout tab I dont have access to any of the fields in my
dataset.
Can anyone help me display the contents of the dataset in SRS.
Many thanks for taking the time to answer this.
SteveB.Have you tried clicking the refresh button on the data tab?
"fisab" wrote:
> Hi, I hope someone will take a moment to answer my question.
> I'm combining results from 2 databases. The SQL statement works fine in
> Query Analyser using the full names of the database tables (db1.dbo.table1,
> db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> when using the master table as the shared datasource.
> However in the layout tab I dont have access to any of the fields in my
> dataset.
> Can anyone help me display the contents of the dataset in SRS.
> Many thanks for taking the time to answer this.
> SteveB.|||Silly oversight of mine - thanks for the help.
Is this the best method of getting data from 2 databases (on the same
server)? I assume I'd only use a linked server if the databases were on
different machines.
"David Siebert" wrote:
> Have you tried clicking the refresh button on the data tab?
> "fisab" wrote:
> > Hi, I hope someone will take a moment to answer my question.
> >
> > I'm combining results from 2 databases. The SQL statement works fine in
> > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > when using the master table as the shared datasource.
> > However in the layout tab I dont have access to any of the fields in my
> > dataset.
> >
> > Can anyone help me display the contents of the dataset in SRS.
> > Many thanks for taking the time to answer this.
> > SteveB.|||As far as I know, that's the best way to get data from both databases. You
might post to the sql discussions group and probably get a more informed
response there.
"fisab" wrote:
> Silly oversight of mine - thanks for the help.
> Is this the best method of getting data from 2 databases (on the same
> server)? I assume I'd only use a linked server if the databases were on
> different machines.
> "David Siebert" wrote:
> > Have you tried clicking the refresh button on the data tab?
> >
> > "fisab" wrote:
> >
> > > Hi, I hope someone will take a moment to answer my question.
> > >
> > > I'm combining results from 2 databases. The SQL statement works fine in
> > > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > > when using the master table as the shared datasource.
> > > However in the layout tab I dont have access to any of the fields in my
> > > dataset.
> > >
> > > Can anyone help me display the contents of the dataset in SRS.
> > > Many thanks for taking the time to answer this.
> > > SteveB.

Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Combining record sets from two tables

Friends,
Can anyone give me the best way to solve the following challenge.
I have two tables in my MS SQLserver database. Table A has this dataset:
code_id code operations_descr
1 M Mob/Demob
2 T Transit
3 O Operation
4 S Standby
5 W WOW
6 C Crew Change
7 B Breakdown
8 R Maintenance
Table B has this dataset:
code_id operations_duration
3 125
3 10
2 1304
I want a new recordset where the sum of the operations_duration from table B
grouped on code_id is combined with the right code_id in table A, like this:
code_id code operations_descr total_operations_duration
1 M Mob/Demob 0
2 T Transit 1304
3 O Operation 135
4 S Standby 0
5 W WOW 0
6 C Crew Change 0
7 B Breakdown 0
8 R Maintenance 0
Thank you for any assistance!
/Leif S
--
Systems AnalystI've assumed that the first table is called "Operations" and that the second
one is called "OperationsDuration". Here is the query:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(OD.Total_Operations_Duration)
From Operations O, OperationsDuration OD
Where O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
Best regards,
Sami Samir
ITWorx
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi John,
The code you gave me works well. But it is more to it.
The data is collected from a survey vessel working in the North Sea and the
tables contains data from daily reports sent from the vessel. Table B stores
operations duration for each day where records from a given day share the
same report_id. When I take your code and add: "Where OD.report_id =
@.reportId" and supply report_id = 2, the result set exclude code lines from
table A that are not in table B like this:
code_id code operations_descr Total_Oper
ations_Duration
2 T Transit 1304
3 O Operation 135
Your advice is appreciated!
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>|||See if this works...
SELECT code_id, code, operations_descr,
operations_duration = (SELECT SUM(total_operations_duration)
FROM TABLEB
WHERE code_id = TABLEA.code_id
GROUP BY code_id
)
FROM TABLEA
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:5E01F636-997E-47A6-A844-206B6DBBE0CA@.microsoft.com...
> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
> B
> grouped on code_id is combined with the right code_id in table A, like
> this:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
I am not sure where report_id comes from as it was not mentioned in the
original post. You may want to check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data.
Try:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.reporti
d
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:
> Hi John,
> The code you gave me works well. But it is more to it.
> The data is collected from a survey vessel working in the North Sea and th
e
> tables contains data from daily reports sent from the vessel. Table B stor
es
> operations duration for each day where records from a given day share the
> same report_id. When I take your code and add: "Where OD.report_id =
> @.reportId" and supply report_id = 2, the result set exclude code lines fro
m
> table A that are not in table B like this:
> code_id code operations_descr Total_Oper
ations_Duration
> 2 T Transit 1304
> 3 O Operation 135
> Your advice is appreciated!
> /Leif S.
> --
> Systems Analyst
>
> "John Bell" wrote:
>|||Thanks, John. So easy!
When I moved the report_id qualifyer away from the "Where" clause and into
the Join statement I got what I wanted.
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> I am not sure where report_id comes from as it was not mentioned in the
> original post. You may want to check out
> http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
> data.
> Try:
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.repor
tid
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>