MellowFellow MellowFellow - 8 months ago 48
SQL Question

SQL Server | Flagging an instance based on multiple lines?

I work in claims data, which has multiple lines in the database.

For example:
Claim: 12345 has 5 lines to it, each paying a different amount for different services.

The issue I am trying to resolve is that each line can either be auto-priced or manually priced. I am trying to flag the data using CASE/WHEN logic so that ANY claim with a manual price flag is present at all on the claim, the FLAG will set the entire claim as "MANUALLY PRICED".

Here is an example of how the data looks in the datawarehouse:

Here is how I would expect the logic to produce:


Here is one way you could accomplish this.

select CLAIMID
    , IsManual.PricingUsed
from Claims c
cross apply
    select max(case when USEMANUAL = 'Y' then 'Yes' else 'No' end) as PricingUsed
    from Claims c2
    where c2.CLAIMID = c.CLAIMID
) as IsManual