Link to home
Create AccountLog in
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.
Avatar of JestersGrind
Flag of United States of America image

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


Avatar of mmr159

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
Avatar of simplyfemales


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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@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.