Is is possible to run a query with multiple lines where one line meets one criteria and the others meet different criteria?

Question

I am trying to identify claims that meet the following criteria:

1. Each line must have denial code "X"
2. One line must have an allowed amount that is greater than $0.00
3. The remaining lines must have an allowed amount equal to $0.00

Example: Claim ID 123456 has three rows associated with this ID. Row 1 allowed $40.00. Rows 2 and 3 allowed $0.00. With this query Claim ID 123456 would pull.

The only way I can think of doing this is running a query for all claims that have denial code "X" and then manually searching in Excel.

Basically the query I have is:

SELECT DISTINCT
Claim ID,
Line Number,
Denial Code,
Line Allowed Amount
(FROM)
WHERE 
Denial Code = 'X'

Answer 1

This sounds like aggregation and filtering with a having clause:

select claimid
from mytable
group by claimid
having 
    min(case when denial = 'X' then 1 else 0 end) = 1   -- all rows have "denial" = "X"
    and max(lineallowedamount) > 0                      -- one row has an amount greater than 0
    and max(lineallowedamount) = sum(lineallowedamount) -- all other rows have amount 0

Answer 2

I would move what we can to the where clause and use having clause to ensure that we only include those claimIds where only one row had amount > 0 with all other being 0, which is only possible when max(lineallowedamount) = sum(lineallowedamount) is true.

select claimId
from your_table
where denial = 'X' and lineallowedamount > 0 
group by claimId
having max(lineallowedamount) = sum(lineallowedamount);

And if you want to get data from your table filtered for these claimids, you can use a subquery like below

select *
from your_table
where claimId in (select claimId
                  from your_table
                  where denial = 'X' and lineallowedamount > 0 
                  group by claimId
                  having max(lineallowedamount) = sum(lineallowedamount));