troubleshooting Question

TOTAL count grouped with UNMATCHED

Avatar of dbaSQL
dbaSQLFlag for United States of America asked on
Microsoft SQL ServerMicrosoft Server OSMicrosoft SQL Server 2008
19 Comments1 Solution420 ViewsLast Modified:
who knows why i am struggling with this.... i am trying to get a total count in databaseA per endpoint, and then a count from databaseA for the same endpoints where not exists in databaseB.   basically, i want this:

databaseA, databaseB, endpoint, totalcount, matchedcount, unmatchedcount

of course, these are all very genericized, but....

--just total counts from both, by endopint
select endpoint,count from databaseA.dbo.tableA group by.....
select endpoint,count from databaseB.dbo.tableA group by.....

--count from databaseA where exists databaseB
select endpoint,count from databaseA.dbo.tableA a where exists(
select 1 from databaseB.dbo.tableA b
where a.endpoint = b.endpoint
and a.orderno = b.orderno)

--count from databaseA where not exists databaseB
select endpoint,count from databaseA.dbo.tableA a where not exists(
select 1 from databaseB.dbo.tableA b
where a.endpoint = b.endpoint
and a.orderno = b.orderno)

--count from databaseB where exists databaseA
select endpoint,count from databaseB.dbo.tableA a where exists(
select 1 from databaseA.dbo.tableA b
where a.endpoint = b.endpoint
and a.orderno = b.orderno)

--count from databaseB where not exists databaseA
select endpoint,count from databaseB.dbo.tableA a where not exists(
select 1 from databaseA.dbo.tableA b
where a.endpoint = b.endpoint
and a.orderno = b.orderno)


I have INNER JOINED and UNIONED both chunks successfully, selectively, but failed miserable to incorporate the two together


i have created a computed column (orderno) which is to uniquely identify ALL data.  90% there, everything looks good..... i'm just trying to check the data out, pre-deployment
see what is in A that doesn't match B, per endpoint, by orderno.  
and then what is in B that does not match A, per endpoint, by orderno

pleeeeeze advise.   terribly, terribly pressing
--never quite got this one functional.  trying to combine the TOTAL count with the unmatched counts

select 'dbname' as SourceDB,a.endpoint,COUNT(*) AS Total,
(select endpoint,COUNT(*) AS UnMatched from database.dbo.tableA a 
where a.timefield BETWEEN @start AND DATEADD(second,2,@stop)
and exists(select 1 from @endpoints e WHERE e.[EndPoint] = a.endpoint)and not exists
	(select 1 from otherdatabase.dbo.tableA b 
	where b.timefield BETWEEN @start AND DATEADD(second,2,@stop)
	and exists(select 1 from @endpoints e WHERE e.[EndPoint] = b.endpoint)
	and a.OrderNo = b.orderno)
	group by a.endpoint) as UnMatched 
from database.dbo.tableA a 
where a.timefield BETWEEN @start AND DATEADD(second,2,@stop)
and exists(select 1 from @endpoints e WHERE e.[EndPoint] = a.endpoint)
group by a.endpoint



--little better here, but not complete.  my resultset is this:

SourceDB    EndPoint      UnMatched   SourceDB    UnMatched
databaseA     XX             83         databaseB     84
databaseA     YY             108        databaseB     108
databaseA     ZZ             2          databaseB     2

format is good, but i'm doubting  the accuracy.  right now, everything from databaseA matches databaseB.  my variance is in databaseB, when referencing databaseA, by endpoint, orderno



declare @start datetime=CONVERT(char(8),getdate(),112), @stop datetime
select @stop = MAX(timefield) FROM dbo.tableA

DECLARE @endpoints TABLE ([EndPoint] varchar(8),PRIMARY KEY([EndPoint]))
INSERT @endpoints VALUES
('...........') ;

SELECT p.SourceDB,p.endpoint,p.UnMatched,d.SourceDB,d.UnMatched from
(
select 'databaseA' AS SourceDB,a.endpoint,COUNT(*) AS UnMatched 
from databaseA.dbo.tableA a 
where a.timefield BETWEEN @start AND DATEADD(second,2,@stop)
and exists(select 1 from @endpoints e WHERE e.[EndPoint] = a.endpoint)and not exists
	(select 1 from databaseB.dbo.tableA b 
	where b.timefield BETWEEN @start AND DATEADD(second,2,@stop)
	and exists(select 1 from @endpoints e WHERE e.[EndPoint] = b.endpoint)
	and a.OrderNo = b.orderno)
group by a.endpoint) p
INNER JOIN
(
select 'databaseB' AS SourceDB,a.endpoint,COUNT(*) AS UnMatched
from databaseB.dbo.tableA a 
where a.timefield BETWEEN @start AND DATEADD(second,2,@stop)
and exists(select 1 from @endpoints e WHERE e.[EndPoint] = a.endpoint)and not exists 
	(select 1 from databaseA.dbo.tableA b where b.timefield BETWEEN @start AND DATEADD(second,2,@stop)
	and exists(select 1 from @endpoints e WHERE e.[EndPoint] = b.endpoint)
	and a.orderno = b.orderno)
GROUP BY a.endpoint) d
ON p.endpoint = d.endpoint
ORDER BY p.endpoint
ASKER CERTIFIED SOLUTION
ralmada

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros