Merge 2 resultsets to compare data

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 resultsets to compare data

Author

Topic

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

103 Posts

Posted - 2006-02-17 : 03:53:10

Hi everyone,
I have the following 2 query's:-

--Shows Total Agent Entries per SM
SELECT SMID, COUNT(DISTINCT AgentID) AS 'No of Entered Agents'
FROM dbo.vw_TimesheetFact
where ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 20, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0 AND site = 'Newsite'
GROUP BY SMID
order by SMID

--Shows Total Agent Entries that are < 80% to Target per SM
SELECT SMID, COUNT(DISTINCT AgentID) AS 'No of Entered Agents'
FROM dbo.vw_TimesheetFact
where perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 20, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0 AND site = 'Newsite'
GROUP BY SMID
order by SMID

Producing:-

The problem I have is that I need to do a comparison between the 2 resultsets to find which SMID's have > 30% of their Agents showing in Resultset2 when compared to Resultset1. I cannot figure what is the best way to go about this, bearing in mind that this eventually will all be wrapped up in a DTS package. Could anyone offer me advice on how I can achieve these results please?

Pat.

mallier
Starting Member

24 Posts

Posted - 2006-02-17 : 04:32:26

u can combined two queries with union.I didnt get ur 30% filter condition on which column.So jus combined ur query.u can write ur filter codition in where clause which I commented

select
SMID,
[No of Entered Agents]
FROM
(
--Shows Total Agent Entries per SM
SELECT
SMID,
COUNT(DISTINCT AgentID) AS 'No of Entered Agents'
FROM
dbo.vw_TimesheetFact
where
([date] BETWEEN CONVERT(varchar, DATEADD(d, - 20, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0
AND site = 'Newsite'
GROUP BY
SMID
union
--Shows Total Agent Entries that are < 80% to Target per SM
SELECT
SMID,
COUNT(DISTINCT AgentID) AS 'No of Entered Agents'
FROM
dbo.vw_TimesheetFact
where
perctarget < 80
AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 20, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0
AND site = 'Newsite'
GROUP BY SMID

) AS tm
--where
order by SMID

cheers,
http://mallier.blogspot.com

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-17 : 05:22:57

Unless I've misunderstood, this should do the trick:

SELECT
  RS1.SMID, 
  RS1.[No Of Entered Agents] AS RS1Count, 
  RS2.[No Of Entered Agents] AS RS2Count
FROM
(
SELECT SMID, COUNT(DISTINCT AgentID) AS 'No of Entered Agents'
FROM dbo.vw_TimesheetFact
where ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 20, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0 AND site = 'Newsite'
GROUP BY SMID
) AS RS1
JOIN 
--Shows Total Agent Entries that are < 80% to Target per SM
(
SELECT SMID, COUNT(DISTINCT AgentID) AS 'No of Entered Agents'
FROM dbo.vw_TimesheetFact
where perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 20, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0 AND site = 'Newsite'
GROUP BY SMID
) AS RS2
ON RS1.SMID = RS2.SMID
WHERE RS2.[No of Entered Agents] > RS1.[No Of Entered Agents] * .3

You'll probably want to change the aliases on the derived tables to something more meaningful than RS1 and RS2!

Mark

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

103 Posts

Posted - 2006-02-17 : 05:47:31

Hi Mark,

This shows all the records from the second query(where Agents < 80%) and their matching rows from the first query combined into one table. This is partly what I want but I also need to filter the results in the new table to only show rows where there is a difference in Agent numbers(ie between RS1Count and RS2Count) of 30% or more.

Pat.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-17 : 06:07:56

Hi Pat
That's what the WHERE clause is doing. It constrains the resultset to those rows where the count from the second derived table is more than 30% (i.e. *.3) of the count in the first derived table for the corresponding SMID. Let me know if I've misinterpreted the logic from your earlier post.

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

103 Posts

Posted - 2006-02-17 : 07:11:13

Hi Mark,

Sorry, you are totaly correct. It was the way I was looking at it (in reverse). Many thanks for your help. I have learned a good lesson in the usages of derived tables.

Pat.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-17 : 07:41:02

No problem

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources