#16
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    No. Out of all the data above the only row that should show is the last one I posted.
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    It will not show any of the rows with Tom in them because on of the rows has approved_amt > 0. The only row that should show is the one with no payments and then if there are multiple rows like this, just show one. Which show only show this:

    Doe | Joe |99214 |3/5/2013| 0.00 |
  4. #18
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    maybe this?
    Code:
    SELECT patient_last_name
         , patient_first_name
         , proc_code
         , DOS
      FROM Claim_base_table 
    GROUP
        BY patient_last_name
         , patient_first_name
         , proc_code
         , DOS
    HAVING COUNT(*) > 1
       AND SUM(approved_amt) = 0
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0

    Not what I am asking


    I need to also filter out the ones if they have amount approved greater than zero.

    Think of it like this if you bill an insurance company for a procedure and they deny your claim 5 items and pay it once. That claim is paid regardless of how many times it was denied. I only want to see a claim that was denied and not paid and I only want to see one instance of it so I can know how many denials I truly have out there.

    You follow me? The code you just gave me shows me the denial but does not filter out the ones that have been paid.
  8. #20
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by dennisdj
    You follow me?
    sorry, i do not

    assuming "not paid" means SUM(approved_amt) = 0 then my query does that

    Comments on this post

    • dennisdj agrees
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #21
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Your query will also show me this one:

    Doe | Jane | Therapy | 1/1/2012 | 0.00

    But since this claim was billed and paid according to the data below it should not be shown.

    Doe | Tom | Therapy | 10/21/2012 | 0.00
    Doe | Tom | Therapy | 10/21/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 0.00
    Doe | Jane | Therapy | 1/1/2012 | 90.00

    So are we at an empasse and what I am trying to do just can't be done?
  12. #22
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by dennisdj
    Your query will also show me this one:
    i really don't think so, because SUM(approved_amt) is ~not~ 0 for those dupes
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    If the amount approved for the same date, proc_code and same name has amount greater than zero one row, then no row with that same date and name should be shown because it should not be considered a duplicate for purposes of what I am trying to show. The query only shows duplicates for rows that have an amount approved as zero and does not take into account if the same name and same date, proc_code has already been paid.
  16. #24
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by dennisdj
    ... and does not take into account if the same name and same date, proc_code has already been paid.
    perhaps you not understand that SUM() operates in a GROUP BY over all the rows for each grouping, i.e. all duplicates

    if the same name, same date, same proc_code has already been paid, then one of those rows will have a non-zero amount, and therefore the total amount paid for all of those rows has to be greater than zero

    Comments on this post

    • dennisdj agrees
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #25
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Oh ok, my apologies I did not catch that. You are absolutely correct, I should have run the code. Once I saw it worked I tried to add other fields I needed to see by modifying the way you gave me earlier and when I use the following code it reverts back to showing me the duplicates. I could probably look at it all day and figure it out but you are probably eager to close this forum out.

    SELECT t.patient_last_name
    , t.patient_first_name
    , t.proc_code
    , t.DOS
    , t.claim_amt
    , status
    , reason_cd
    FROM (SELECT patient_last_name
    , patient_first_name
    , proc_code
    , DOS
    FROM Claim_base_table
    GROUP
    BY patient_last_name
    , patient_first_name
    , proc_code
    , DOS
    HAVING COUNT(*) > 1
    AND SUM(approved_amt) = 0 ) AS dupes
    INNER
    JOIN Claim_base_table AS t
    ON t.patient_last_name =dupes.patient_last_name
    AND t.patient_first_name = dupes.patient_first_name
    AND t.proc_code = dupes.proc_code
    AND t.DOS = dupes.DOS;
  20. #26
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by dennisdj
    ... I tried to add other fields I needed to see by modifying the way you gave me earlier and when I use the following code it reverts back to showing me the duplicates.
    think about what you said here

    you have a subquery (called dupes) which returns one aggregate row for each group of duplicates

    now if you join that aggregate row back to the individual rows, then of course you're going to see duplicates

    all of those duplicate rows contain a status and reason_cd, but if you don't want duplicates, then which of the rows in each group should be selected to show the status and reason_cd? are these values going to be the same on all duplicate rows in each group?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  22. #27
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    there is a field called adjudication_date, I will like to have the row with the lastest adjudication_date.
  24. #28
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by dennisdj
    there is a field called adjudication_date, I will like to have the row with the lastest adjudication_date.
    please be very specific now, when you answer this next question --

    do you want just the latest adjuducation date for the group of duplicates, or is there yet some other column that you have to see from the row with the latest adjudication date?

    because it makes a huge difference to the query
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  26. #29
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    please be very specific now, when you answer this next question --

    do you want just the latest adjuducation date for the group of duplicates, or is there yet some other column that you have to see from the row with the latest adjudication date?

    because it makes a huge difference to the query
    I have to see other columns but just the ones that have the latest adjudication date.
  28. #30
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    SELECT t.patient_last_name
         , t.patient_first_name
         , t.proc_code
         , t.DOS
         , t.adjudication_date
         , t.claim_amt
         , t.status
         , t.reason_cd
      FROM ( SELECT patient_last_name
                  , patient_first_name
                  , proc_code
                  , DOS
                  , MAX(adjudication_date) AS latest
               FROM Claim_base_table 
             GROUP
                 BY patient_last_name
                  , patient_first_name
                  , proc_code
                  , DOS
             HAVING COUNT(*) > 1
                AND SUM(approved_amt) = 0 ) AS dupes
     INNER
       JOIN Claim_base_table AS t
         ON t.patient_last_name =dupes.patient_last_name 
        AND t.patient_first_name = dupes.patient_first_name 
        AND t.proc_code = dupes.proc_code 
        AND t.DOS = dupes.DOS
        AND t.adjudication_date = dupes.latest
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo