如何加快我使用union的查询

问题描述

以下查询需要更长的时间。

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

请帮忙让它运行快速



我尝试过：


带有QI的


as

（选择来自tr_QuoteInfo qp的QuoteID，LOBID，Status，CarrierID，Premium，其中QuoteID不在（从pre_otein不为空的tr_QuoteInfo中选择PreQuoteID）和LOBID = 1

union

选择来自tr_QuoteInfo的QuoteID，LOBID，Status，CarrierID，Premium，其中PreQuoteID不为空且LOBID = 1）



选择CQ.AssignTo，Count（qi.QuoteID）作为TotalQotes，Count（qi.Status！= 4然后qi.QuoteID结束时的情况）为LostQuotes，Count（qi.Status = 4然后qi的情况。 QuoteID结束）作为更新， Sum（当qi.Status = 4然后qi.premium结束时的情况）作为CARClosedPremium，Sum（qi.Status！= 4然后qi.premium结束时的情况）作为CARLostPremium

来自tr_CRM_QuoteInfo CQ加入QI on CQ.QuoteID = qi.QuoteID和qi.LOBID = cq.LOBID



在QI上加入tr_MotorPraposalReqRes MPR.QuoteID = MPR.QuoteID和QI.LOBID = MPR.LOB和QI.CarrierID = MPR.CarrierID





其中DATEDIFF（dd，getdate（），convert（date，MPR.PolicyExpDate，103） ））0到60之间

分组由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

推荐答案

这篇关于如何加快我使用union的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！