?
Solved

TOTAL count grouped with UNMATCHED

Posted on 2009-12-16
19
Medium Priority
?
370 Views
Last Modified: 2013-11-21
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

Open in new window

0
Comment
Question by:dbaSQL
  • 13
  • 3
  • 2
  • +1
19 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 26066600
select 'dbname' as SourceDB,a.endpoint,COUNT(*) AS Total,
(select endpoint,COUNT(*) 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
 ) x


getting closer.... some input would be truly cool
0
 
LVL 35

Expert Comment

by:YZlat
ID: 26066662
what is the problem t this point?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26066714
many fold......  :-)
I don't know that the last post is the correct way to approach this... i feel as though the INNER JOIN may be better, but I am unable to get all my constructs into the JOIN.   the last post is now failing with this:


Msg 116, Level 16, State 1, Line 23
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "a.SourceDB" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "a.EndPoint" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "a.Total" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "a.UnMatched" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "b.SourceDB" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "b.Total" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "b.UnMatched" could not be bound.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:dbaSQL
ID: 26066749
now we're just down to this:
Msg 116, Level 16, State 1, Line 23
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

pointing here:        group by a.endpoint) as UnMatched  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26066884
changed the last code posted to this to get around that EXISTS problem:

select 'dbname' as SourceDB,a.endpoint,COUNT(*) AS Total,
(select COUNT(EndPoint)
from database.dbo.tableA a  
...............
...........
group by endpoint) AS UnMatched
.........
.....


now it runs for :03 and pukes with this:

(18 row(s) affected)
Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26067024
It's difficult to tell which one is your last query. But please check this query
select 	'dbname' as SourceDB,
	t1.endpoint,
	COUNT(*) AS Total, 
	(select COUNT(*) 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) 
		        and t1.endpoint = a.endpoint) as UnMatched  
from database.dbo.tableA t1  
where t1.timefield BETWEEN @start AND DATEADD(second,2,@stop) 
and exists(select 1 from @endpoints e WHERE e.[EndPoint] = t1.endpoint)

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067103
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AND'.

pointing here:                           and t1.endpoint = a.endpoint) as UnMatched  

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067120
pulled out that right paren from a.orderno = b.orderno
it then failed with:

Msg 8120, Level 16, State 1, Line 16
Column 'database.dbo.tableA.endpoint' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "b.endpoint" could not be bound.
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "b.orderno" could not be bound.


put the group by endpoint in there, and now i've just got the multi-part identifier errors
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26067121
an extra ) at the end of the line before:
                        and a.OrderNo = b.orderno)  <-- Remove that
                        and t1.endpoint = a.endpoint) as UnMatched  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067158
unless i'm misreading, i've already handled that, cg.  possibly we posted at the same time... or i'm not reading you right

now i've got the two multi-part errors

Msg 4104, Level 16, State 1, Line 22
The multi-part identifier "b.endpoint" could not be bound.
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "b.orderno" could not be bound.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26067172
yes, we did.
Are you working from ralmada's last code post or can you repost what you are using if it is different?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26067232
what about this

select 	'dbname' as SourceDB,
	t1.endpoint,
	COUNT(*) AS Total, 
	(select COUNT(*) 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] = a.endpoint) 
		        and a.OrderNo = t1.orderno 
		        and t1.endpoint = a.endpoint) as UnMatched  
from database.dbo.tableA t1  
where t1.timefield BETWEEN @start AND DATEADD(second,2,@stop) 
and exists(select 1 from @endpoints e WHERE e.[EndPoint] = t1.endpoint) 
group by t1.endpoint

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067269
this one is good.....


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



i get back data.... but my counts are way wrong


basically, total count is good, but everything that is returned for unmatched is waaaaaaaay off.  
for example, one of my endpoints is XX, I've got 131 records in both databases, neither have any unmatched orderno's


the resultset, however, returns this:

sourcb        endpoint     total          unmatched
databaseA   xx              131            136761

then theres endpoint YY with a total of 3772 records.  again, none are unmatched.  it returns this:

sourcb        endpoint     total          unmatched
databaseA   yy              3772           133120

then we've got endoint ZZ, with a total count of 2234.
all 2234 records in databaseA match databaseB, by orderno
there are, however, 108 records in databaseB that don't match databaseA, by orderno

my result gives back this:

sourcb        endpoint     total          unmatched
databaseA   zz              2234           134766

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067273
let me try that, ralmada
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067343
by the way, when i said 'good' up there, i was being seriously sarcastic...
i'm trying the latest, ralmada, but syntax issues on that orderno not being in the group by.

working on it....
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067363
not counting that orderno.  unsure why i'd be getting the 8120 on this.... hmmm.....

Msg 8120, Level 16, State 1, Line 21
Column 'database.dbo.tableA.OrderNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26067456
have to run.   still haven't gotten around that.  back soon... anxious to see what you may suggest
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 26068564
Can you try this? . Please note that "database" is a keyword so you must use change it with the correct database name.
 

select	'dbname' as SourceDB, 
		t1.endpoint, 
		COUNT(*) AS Total,  
		sum(case when t2.endpoint is null then 1 else 0 end) as unmatched
from [database].dbo.tableA t1
left join (select * from otherdatabase.dbo.TableA where timefield between @start and DATEADD(second,2,@stop)) t2 on t1.endpoint = t2.endpoint and t1.orderno = t2.orderno
where	t1.timefield between @start AND DATEADD(second,2,@stop) and
		exists(select 1 from @endpoints e WHERE e.[EndPoint] = t1.endpoint) 
group by t1.endpoint

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 26068673
i believe that is it, ralmada.   or very close to it, i should say....

this is what i wanted:   databaseA, databaseB, endpoint, totalcount, matchedcount, unmatchedcount
this is what ive got:      sourceDB, endpoint, total, unmatched

i was actually getting that already (yesterday when i first started counting this stuff), but I was unsure that it was accurate, and it was very hacky/manual.

i suppose, if 'sourcedb' = A, this implies databaseB.   and if 'total' = this, and 'unmatched' = that, i can go ahead and do the math for 'matched'.   will do that now. back shortly.




i typed that... then i went and did my work, forgot to hit 'submit'
it's all good.  very good.

big thank you, ralmada
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question