CODEWITHSUNDEEP
sql-servert-sqlcase
Serdia
Serdia

Reputation: 4428

How to not pick up a record that have a priority based on columns names

One of the column Im picking up is PolicyType and the condition for that is TransactionType = 'Policy'. But sometimes the same Insuredcan have same TransactionType value but different PolicyType value. So if I have the same insured with same TransactionType but different PolicyType - how can prioritize and choose the record that has PolicyType='Rewrite'and ignore the one withPolicyType='New Business'? Should be simple CASE statement but I am confused enter image description here

SELECT   CAST(YEAR(EffectiveDate) as CHAR(4))+'/'+ CAST(MONTH(EffectiveDate) AS VARCHAR(2)) AS YearMonth,
         Insured, 
        PolicyNumber,
        EffectiveDate,
        PolicyType,
        TransactionType,
        SUM(Premium) as Premium,
        ExperienceMod,
        ISNULL(ScheduleMod,0) as ScheduleMod,
        TerritoryMod,
        ISNULL(EffectiveMod,0) as EffectiveMod

FROM ProductionReportMetrics
WHERE EffectiveDate >=DateAdd(yy, -1, DATEADD(d, 1, EOMONTH(GETDATE()))) AND EffectiveDate <= EOMONTH(GETDATE())
        AND CompanyLine = 'Arch Insurance Company' AND Insured <>'Jerry''s Test' AND TransactionType = 'Policy' --and  PolicyNumber ='ZAWCI2517900'
GROUP BY Insured,
        PolicyNumber,
        PolicyType,
        TransactionType,
        EffectiveDate,
        experienceMod,ScheduleMod,TerritoryMod,EffectiveMod, Premium,EffectiveDate
ORDER BY YEAR(EffectiveDate), (MONTH(EffectiveDate)), PolicyType

Upvotes: 0

Views: 38

Answers (1)

Dance-Henry
Dance-Henry

Reputation: 953

After checking your sample data, I suppose the priority for the policyType column is rewrite > renewal > new business. :)

Therefore here comes the solution, please change the code accordingly:

--Create table
create table #test (id int identity(1,1), name varchar(10), trantype varchar(10) default 'policy', policytype varchar(50))

--insert sample data
insert #test (name,trantype,policytype)
select 'abc','policy','new business'
union all
select 'abc','policy','rewrite'
union all
select 'AA','policy','new business'
union all
select 'BB','policy','new business'
union all
select 'BB','policy','rewrite'
union all
select 'CC','policy','rewrite'

select * from #test

--solution
select * from 
(select *, row_number()over(partition by name,trantype order by policytype desc) as rid 
 from #test
) as b
where b.rid = 1

RESULT:

(1)If only new business was there for a specified insured, then it will be selected;

(2)If both new business and rewrite are there for a certain insured, then only rewrite will be selected.

enter image description here

Upvotes: 2

Related Questions