The below query taking longer time.
with QI
as
(select QuoteID, LOBID, Status,CarrierID, Premium from tr_QuoteInfo qp where QuoteID not in (select PreQuoteID from tr_QuoteInfo where PreQuoteID is not null) and LOBID =1
union
select QuoteID, LOBID, Status, CarrierID, Premium from tr_QuoteInfo where PreQuoteID is not null and LOBID =1)
select CQ.AssignTo,Count(qi.QuoteID) as TotalQotes,Count(case when qi.Status != 4 then qi.QuoteID end) as LostQuotes, Count(case when qi.Status= 4 then qi.QuoteID end) As Renewed, Sum(case when qi.Status= 4 then qi.premium end) as CARClosedPremium , Sum(case when qi.Status != 4 then qi.premium end) as CARLostPremium
from tr_CRM_QuoteInfo CQ join QI on CQ.QuoteID = qi.QuoteID and qi.LOBID = cq.LOBID
join tr_MotorPraposalReqRes MPR on QI.QuoteID=MPR.QuoteID and QI.LOBID=MPR.LOB and QI.CarrierID=MPR.CarrierID
where DATEDIFF(dd,getdate(),convert(date,MPR.PolicyExpDate,103)) between 0 and 60
Group by CQ.AssignTo
Please help to make it run Quick
What I have tried:
with QI
as
(select QuoteID, LOBID, Status,CarrierID, Premium from tr_QuoteInfo qp where QuoteID not in (select PreQuoteID from tr_QuoteInfo where PreQuoteID is not null) and LOBID =1
union
select QuoteID, LOBID, Status, CarrierID, Premium from tr_QuoteInfo where PreQuoteID is not null and LOBID =1)
select CQ.AssignTo,Count(qi.QuoteID) as TotalQotes,Count(case when qi.Status != 4 then qi.QuoteID end) as LostQuotes, Count(case when qi.Status= 4 then qi.QuoteID end) As Renewed, Sum(case when qi.Status= 4 then qi.premium end) as CARClosedPremium , Sum(case when qi.Status != 4 then qi.premium end) as CARLostPremium
from tr_CRM_QuoteInfo CQ join QI on CQ.QuoteID = qi.QuoteID and qi.LOBID = cq.LOBID
join tr_MotorPraposalReqRes MPR on QI.QuoteID=MPR.QuoteID and QI.LOBID=MPR.LOB and QI.CarrierID=MPR.CarrierID
where DATEDIFF(dd,getdate(),convert(date,MPR.PolicyExpDate,103)) between 0 and 60
Group by CQ.AssignTo