Merge 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Merge 2 queries

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-17 : 05:21:21

I cannot merge these 2 seperate queries. Can anyone show me how please?

1.
set dateformat dmy

select * from (
select distinct PERSONKEYIDS.FullName AS 'Sales Manager Name',
PERSONKEYIDS.PersonKeyId AS 'ID', convert(varchar,max(convert(datetime,Timesheets.[date])),103) as [Last Date Entered]

from AGENT_TEAM_FACT join PERSONKEYIDS
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
left outer join Timesheets
on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
group by PERSONKEYIDS.FullName, PERSONKEYIDS.PersonKeyId) z
order by convert(datetime,[Last Date Entered])

2.
(select distinct CAMPAIGNS.Site
from CAMPAIGNS inner join PERSONKEYIDS
on CAMPAIGNS.PersonKeyId = PERSONKEYIDS.PersonKeyId)

Ive tried to simply add no.2 as another join and its gives me an incorrect result set.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 05:44:38

To merge two queries they should return the resultset which are identitcal in data types and the number of columns. Explain what you are trying to do

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-17 : 05:52:15

I just want to include the Campaigns.Site column query 1 results.

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-17 : 06:04:24

From CAMPAIGNS with (Nolock) inner join PERSONKEYIDS with (nolock)
on CAMPAIGNS.PersonKeyId = PERSONKEYIDS.PersonKeyId
inner join AGENT_TEAM_FACT with (Nolock)
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
left outer join Timesheets with (Nolock)
on PERSONKEYIDS.PersonKeyId = Timesheets.SMID

Use this in your query and add CAMPAIGNS.Site column in select and group by.

Surendra

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-17 : 06:07:01

Sorry, what I meant to say was that I just want to include the 'Campaigns.Site' column in the query 1 results, so that the SELECT statement would look like:

PERSONKEYIDS.FullName AS 'Sales Manager Name',
PERSONKEYIDS.PersonKeyId AS 'ID', convert(varchar,max(convert(datetime,Timesheets.[date])),103) as [Last Date Entered],
--And Also
CAMPAIGNS.Site

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-17 : 06:10:21

select * from (
select distinct PERSONKEYIDS.FullName AS 'Sales Manager Name',
PERSONKEYIDS.PersonKeyId AS 'ID', convert(varchar,max(convert(datetime,Timesheets.[date])),103) as [Last Date Entered],
CAMPAIGNS.Site
From CAMPAIGNS with (Nolock) inner join PERSONKEYIDS with (nolock)
on CAMPAIGNS.PersonKeyId = PERSONKEYIDS.PersonKeyId
inner join AGENT_TEAM_FACT with (Nolock)
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
left outer join Timesheets with (Nolock)
on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
group by PERSONKEYIDS.FullName, PERSONKEYIDS.PersonKeyId, CAMPAIGNS.Site ) z
order by convert(datetime,[Last Date Entered])

Surendra

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-17 : 06:33:05

Hi Surendra,
I tried this and I get the same problems that I was getting the other day. The record count is actually decreased from 131 to 113. Also, some of this data is incorrect.

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-17 : 07:11:48

In your previous query CAMPAIGNS table was not there, but in new query you have added that table as well as the condition to check the PersonKeyId in PERSONKEYIDS table.
Remember this is an additional filter in your result. Because of that it may be showing the less number of rows in result.
Only you can analyze your data whether it is showing correct or wrong. If it is wrong then it is very difficult to identify without having table structure and data.

Surendra

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-17 : 07:43:01

quote:
with (nolock)

Oi! Stop that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources