I work in claims data, which has multiple lines in the database.
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 , 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