Avatar of simplyfemales
 asked on

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

I already know which data did not meet the SQL query, but I am not sure why.  Upon my review it appears that it should have been part of the resultant set.

Is there a way to find out why the excluded data did not qualify for the query? I have 521 records out of just over 307,000 that were excluded but I don't think should have been.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

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



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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

@matthewspatrick, good pick!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.