How to exclude row with date

Posted on 2011-04-25
Medium Priority
Last Modified: 2012-05-11
I need to exclude data rows that have same auth_no when the discharge date is not null in any row.
So in the example below, all the rows with auth_no = 200 should be excluded because the last row has a discharge date.  The report should only display the first row of data where auth_no = 100.   Any sql help would be appreciated.

Service Date      auth_no      Discharge
1/5/2011      100      NULL
1/14/2011      200      NULL    --should be exluded
1/18/2011      200      NULL    --should be excluded
1/20/2011      200      NULL    --should be excluded
2/4/2011      200      1/22/2011  -- all auth_no = 200 should be excluded as discharge date is not null in this row.
Question by:lcalabro
LVL 32

Accepted Solution

Ephraim Wangoya earned 2000 total points
ID: 35463427
;with CTE
( select auth_no, max(Discharge) Discharge, COUNT(auth_no) DUP
  from table1
  group by auth_no
 select * 
 from table1 A
 where exists ( select 1 
                from CTE
                where auth_no = A.auth_no
                and DUP =1
                and Discharge is null)

Open in new window

LVL 21

Expert Comment

by:Alpesh Patel
ID: 35464982
Select * From Table where  auth_no <> 200

Author Comment

ID: 35773096
thank you!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

840 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