Combining two select statements

I have a SP returning the following result
The select statement for this is

Code:

SELECT dbo.TEST1.[OFFICE NAME],COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCountFROM dbo.Test2INNERJOIN dbo.test3INNERJOIN dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryIDWHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)GROUP BY dbo.TEST1.[OFFICE NAME]ORDER BYCOUNT(dbo.TEST1.[ACCOUNT ID])DESC

name id count

case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73

i have another select stnat which returns like this
The select statement is

Code:Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name]
order by count(test1.[office name]) DESC

name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177

How can i combine this select stament with the SP, so that, i get a fourth column with

case1 226 320 74
case2 219 288 56
....................
.....................

Hope i am not confusing you all
Please help me, if someone knows how to combine this?

Thanks

Use an alias for the Office Name column for both statements and add the id column to your first select( you need to add this column to your the GROUP BY list). Then you can use an INNER JOIN on this name column and retrieve all three columns.

Something like:

SELECT t1.name, t1.id, t1.AccountCount, t2.AccountCount2 FROM (SELECT dbo.TEST1.[OFFICE NAME] as name, [ACCOUNT ID] as id,COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCount
FROM dbo.Test2INNERJOIN
dbo.test3INNERJOIN
dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN
dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN
dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)
GROUP BY name, id ) t1 INNER JOIN (Select test1.[office name] as name, count(test1.[office name]) as AccountCount2 From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name] ) t2 ON t1.name=t2.name
ORDER BY t1.AccountCount DESC

|||

I think you've forgotten a column in your first select statement. Your first select statement selects only two columns while the output shows three columns, name, id and count. Please check and repost.

Combining Stored Procedures

I have two stored procedures

ALTER PROCEDURE dbo.qryCountOne
(@.inputID int)
AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
(dbo.TableOne.value = @.inputID)

ALTER PROCEDURE dbo.qryCountTwo
(@.inputID int)
AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
(dbo.TableTwo.value = @.inputID)

What would be the best way to combine these two, so that I only have to
make one database query, and the two values (CountOne, and CountTwo)
will get returned to me?

Any help\pointers greatly appreciated,

Noel"Noel" <vbgooglegroups@.yahoo.com> wrote in message
news:1120752722.113719.37280@.g44g2000cwa.googlegro ups.com...
>I have two stored procedures
> ALTER PROCEDURE dbo.qryCountOne
> (@.inputID int)
> AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
> (dbo.TableOne.value = @.inputID)
> ALTER PROCEDURE dbo.qryCountTwo
> (@.inputID int)
> AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
> (dbo.TableTwo.value = @.inputID)
> What would be the best way to combine these two, so that I only have to
> make one database query, and the two values (CountOne, and CountTwo)
> will get returned to me?
>
> Any help\pointers greatly appreciated,
> Noel

Output parameters are usually the best way to return scalar values from a
stored proc, so perhaps something like this?

create proc dbo.GetRowCounts
@.TableOneID int
@.TableOneCount int OUTPUT,
@.TableTwoID int,
@.TableTwoCount int OUTPUT
as
begin
select @.TableOneCount = count(*)
from dbo.TableOne
where col = @.TableOneID

select @.TableTwoCount = count(*)
from dbo.TableTwo
where col = @.TableTwoID
end

If you have to use a result set instead of output parameters, then see
"UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
MSSQL, so if that is the real column name, you might want to consider
changing it if possible - see "Reserved Keywords" in BOL.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> If you have to use a result set instead of output parameters, then see
> "UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
> MSSQL, so if that is the real column name, you might want to consider
> changing it if possible - see "Reserved Keywords" in BOL.

It's listed among the "Future keywords". Given the record of SQL Server
I would not hold my breath until all those words become reserved.

T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> T-SQL has this funny notion of unreserved keywords, and they seem to grow in number with every release.<<

They got that idea from ANSI, which has such a list when we were
looking at the SQL3 working draft.|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.<<
> They got that idea from ANSI, which has such a list when we were
> looking at the SQL3 working draft.

Nah, I was thinking of things like OUTPUT - which must have been around
since the 80s. OUTPUT is a keyword, but it's not reserved and you
can create a table or a column with that name, without any quoting.

But I assume you were thinking of the list of "Future keywords". That
does indeed seem like an ANSI list.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's great, thanks!

Noel|||Yes, I agree it's unlikely to be a problem, but I generally prefer to
recommend that people follow best practices as documented by Microsoft.
For me, that's a better option than assuming that something has never
been a problem in the past, so it's going to be OK in the future (cf
the short article in this month's SQL Server Magazine on xp_reg% procs
behaviour in SP4).

Simon

Combining fields

Hello,
Within a view I've created, I have combined 2 fields to make 1.
dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
This is for display (to populate a listbox in .NET).
The problem is, if there is nothing in the STR_LAYER field, the whole field
is blank.
Is it possible to display Stratum always, and Layer when it's available?
Thanks!
AmberUse functions ISNULL or COALESCE.
Example:
coalesce(dbo.TABLE1.STR_STRATUM + N' ', N'') +
coalesce(dbo.TABLE2.STR_LAYER, '') AS StratumLayer
AMB
"amber" wrote:

> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole fiel
d
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||SELECT dbo.TABLE1.STR_STRATUM + ISNULL( N' ' + dbo.TABLE2.STR_LAYER AS
StratumLayer, '')
Jacco Schalkwijk
SQL Server MVP
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||If you concatenate a string with a null value, it will return null.
Use ISNULL function:
dbo.TABLE1.STR_STRATUM + N' ' + ISNULL(dbo.TABLE2.STR_LAYER ISNULL(), '')
Francesco Anti
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||This worked.
Thanks!
Amber

Combine Rows in Search Result

In Sql Server 2005 Express I have this table:

CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)

Each paragraph of text (SectionText) is in its own row(SectionNo) Each primary document has a DocumentTypeID of 1 withthree subdocument types (2=Index, 3=Background, 4=Report).

I run this query and return a collection of single rows from various documents grouped together by DocumentNo:

SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo

For each row that contains the search term, I would like toreturn the full document (all rows as parapraphs within one row ofreturned data). In other words, I want to reconstitute the fulldocument as it existed prior to being inserted into the database withparagraph separation.

For exampe, if the search term is in row 3of DocumentNo=5, DocumentTypeID=2, I want to return all the rows ofthat document in one block of text that retains paragraph format(preferablly with a line break and carriage return betweenparagraphs). How can this be done?

You can do this trick which will lead you to solve the problem.

Okay, let say you need to group each page's paragraph in one record insted of many records (as in your current case).

Step#1:

So, Create another table with following columns :
1) BookID: Int or smallint
2) PageID: Int or smallint
3) PageText: Text or NText

Step#2:

1) Do acursor that will loop throug all of theparagraphs related to aspecific page.
2) DoINSERT thefirst record into thePageText field of thenew created table, while you doUPDATEfor therest of recordsafter concatenatingthem with value already exists in thePageText field.

Step#3:

Do this for each page in each book.

Result:

At the end you will have one table from which you can query and seach about any word/paragraph in any page in any book!!

Good luck.

|||

Thanks for the suggestion. I will give it a try.

Columns not updating from Stored procedure

Ok, so I've got the following stored procedure:

ALTER PROCEDURE dbo.tbUserPreferences_UpdateOrInsert

(
@.username varchar(50),
@.preferences varchar(300),
@.view_name varchar(300),
@.default_view varchar(10) = 'Y'
)

AS
UPDATE tbUserPreferences SET @.default_view='N' WHERE username=@.username

-- IF NOT EXISTS (
-- SELECT *
-- FROM tbUserPreferences
-- WHERE username=@.username
-- AND view_name=@.view_name
-- )
-- INSERT INTO tbUserPreferences (username, preferences,view_name,default_view) VALUES (@.username,@.preferences,@.view_name,@.default_view)
RETURN

The commented out section works fine, but the UPDATE line does not. I know there are columns that have "username=@.username", but this call is not updating their default_view column.

Please, if anybody knows why, let me in on the secret. Thanks!Try to execute and check result:

UPDATE tbUserPreferences SET @.default_view='N' WHERE username='your sp param'

select @.@.rowcount|||Wow, I'm so silly. And it took me looking at your reply to get it.

The code I ment to try was:

UPDATE tbUserPreferences SET default_view='N' WHERE username=@.username

"default_view" not "@.default_view". Thank you for the reply. Even though I didn't need to test your suggestion, it made me realize my problem. Thanks!|||It's still a good example of why you should error check your code...

Column name in functions

Hi,

Can we use a parameter that is a column name in a function ?

Here's my function :

CREATE FUNCTION dbo.fn_counting (@.colnumber varchar(2),@.number
varchar(1))
RETURNS int AS

BEGIN
DECLARE @.column varchar(2)
DECLARE @.ColTotal int

SET @.column = 'R' +@.colnumber
(This next line WORKS !!!)
SELECT @.ColTotal = COUNT(*) FROM dbo.Tbl_Answers WHERE R3 = @.number
(This next one DOESN'T WORK - because of the ' it is treated as a
string)
SELECT @.ColTotal = 'COUNT(*) FROM dbo.Tbl_Answers WHERE ' +@.column +
'=' +@.number

RETURN @.ColTotal
END

Thank youNo. But with good design you should never need to. Why wouldn't you know the
column name at design time?

--
David Portas
SQL Server MVP
--|||Because my data table is filled with 40 answers (columns) from a survey
(4,3,2,1) for different group. Then the user will tell me which group,
year, etc he needs the data for and I need to count the number of
4,3,2,1 for that groups for every answer (column). Not really clear !!!

But obviously you are right I will rethink my approach

Thank you for the answer|||For example, try this:

CREATE TABLE Survey (group_no INTEGER NOT NULL REFERENCES Groups (group_no),
year_no INTEGER NOT NULL, answer_no INTEGER NOT NULL CHECK (answer_no
BETWEEN 1 AND 40), response INTEGER NOT NULL CHECK (response BETWEEN 1 AND
4), PRIMARY KEY (group_no, year_no, answer_no))

SELECT response, COUNT(*)
FROM Survey
WHERE group_no = @.group_no
AND year_no = @.year_no
GROUP BY response

--
David Portas
SQL Server MVP
--|||Patrik (patrik.maheux@.umontreal.ca) writes:
> Because my data table is filled with 40 answers (columns) from a survey
> (4,3,2,1) for different group. Then the user will tell me which group,
> year, etc he needs the data for and I need to count the number of
> 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> But obviously you are right I will rethink my approach

You should most certainly make the columns into rows instead. The way
databases work, it's a lot easier to handle repeating groups if they
are rows instead of columns.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think cannot make my columns into rows because the data comes like
that from an optical reader in a text format that I import.Let me be
clearer :

My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
I can have 125 respondants(rows) for one code thus the autoid
DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
like these

Then I need to count the number of 4-3-2 and 1 for every personcode.

I will try the proposed solution and let the group know if it works

Thank you again for the help

Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Because my data table is filled with 40 answers (columns) from a survey
> > (4,3,2,1) for different group. Then the user will tell me which group,
> > year, etc he needs the data for and I need to count the number of
> > 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> > But obviously you are right I will rethink my approach
> You should most certainly make the columns into rows instead. The way
> databases work, it's a lot easier to handle repeating groups if they
> are rows instead of columns.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||The format the data is supplied in should not dictate the database design.
Design the database correctly and then develop a process to load the data
into that database from its external source.

--
David Portas
SQL Server MVP
--|||Patrik (patrik.maheux@.umontreal.ca) writes:
> I think cannot make my columns into rows because the data comes like
> that from an optical reader in a text format that I import.Let me be
> clearer :
> My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
> I can have 125 respondants(rows) for one code thus the autoid
> DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
> like these
> Then I need to count the number of 4-3-2 and 1 for every personcode.
> I will try the proposed solution and let the group know if it works

As David said, don't let the input format dictate your data model. That
format will give you a headache somewhere on the line, and I'm telling
you the earlier you handle it in the process, the less headache you will
get.

For this case, I would unpack the string with a list-to-table function,
see http://www.sommarskog.se/arrays-in-...ist-of-integers
for such a function. For your case you would have handle listpos 1, 2
and 3 individually, and then the answers would be everything above 4.
You could use the function as is, but you could also adapt it so it
directly unpacks into the format you need.

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

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