MellowFellow MellowFellow - 24 days ago 10
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:
http://imgur.com/a/QI4iC

Here is how I would expect the logic to produce:
http://imgur.com/a/kNTVm

Answer

Here is one way you could accomplish this.

select CLAIMID
    , CLAIMLINE
    , USEMANUAL
    , 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