SQL: Find out why data didn't meet query criteria?

It's hard to say without seeing the query, but something excluded those records, either criteria in the where clause or through a join.

Greg

Definitely need more to go on here.  Please provide the query and a few records that you believe should not have been included.

Is there any INNER JOIN in your query? remember, Inner join will not include any record that didn't meet criteria on both side. sometimes you are focusing on the aspect you are staring on and over looked that an inner join has filter the subset of records.

Can you post your code here? It will be easier to help you direct on it

I know I was shooting in the dark and being vague, but unfortunately I can't provide any of the records due to HIPAA and it would just take too damn long to dummy them up.

However, yes, there is an inner join contained in the query.  I will start by looking at both sides and see if any red flags jump out.

The JOIN is actually a LEFT JOIN rather than an INNER JOIN, which to my knowledge should mean that everything identified in the LEFT table should show up regardless.  That's where my confusion lies.  These 521 records are part of that table, but they are being excluded.

Here's the query.

with testing as
(select t1.posted, t1.GNo, t1.ABC 'Original ABC', t2.[service date], t2.ndc 'Matching ABC', t2.[GDV per unit], row_number() over (partition by t1.posted, t1.ABC, t1.GNo, t1.Fee order by t2.ABC desc) as RowNum
from PartA t1 left join FEEDS.dbo.GDV_list t2
on t1.ABC=t2.ABC
and t2.[service date]<=t1.posted
where t1.ABC is not null)
select *
from testing
where rownum=1

@matthewspatrick, good pick!!

sorry.  nothing working, but @matthewspatrick seems reasonable.  I clearly have something not set up properly.  Closing for now.  May revisit if I can't get it figured out.