How to exclude row with date

Posted on 2011-04-25
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

    ;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
    Select * From Table where  auth_no <> 200

    Author Comment

    thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now