Please suggest some better ways to rewrite the query to improve the query performance, especially the date report parameter parts. Thank You.
ALTER PROCEDURE [dbo].[SponsorInvoiceReport]
@ContractBU NVARCHAR(MAX),
@ContractSBU NVARCHAR(MAX),
@Sponsor NVARCHAR(MAX),
@QuotedBy NVARCHAR(MAX),
@DecisionDateFrom DATETIME,
@DecisionDateTo DATETIME,
@ActivityStartDateFrom DATETIME,
@ActivityStartDateTo DATETIME,
@ActivityEndDateFrom DATETIME,
@ActivityEndDateTo DATETIME,
@ActivitySBU NVARCHAR(MAX),
@ActivityIntercoSBU NVARCHAR(MAX),
@ContractCode NVARCHAR(MAX),
@GroupByCPCode BIT
AS
BEGIN
SET NOCOUNT ON
DECLARE @NewDecisionDateTo DATETIME
SET @NewDecisionDateTo = DATEADD(ss,-1,DATEADD(dd,1,CAST(CAST(@DecisionDateTo AS DATE)AS DATETIME)))
DECLARE @NewActivityStartDateTo DATETIME
SET @NewActivityStartDateTo = DATEADD(ss,-1,DATEADD(dd,1,CAST(CAST(@ActivityStartDateTo AS DATE)AS DATETIME)))
DECLARE @NewActivityEndDateTo DATETIME
SET @NewActivityEndDateTo = DATEADD(ss,-1,DATEADD(dd,1,CAST(CAST(@ActivityEndDateTo AS DATE)AS DATETIME)))
DECLARE @Delimiter AS CHAR(1) =','
DECLARE @XML AS XML
---ContractBU
SET @XML = CAST(('<code>'+REPLACE(@ContractBU,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #ContractBU (ContractBUCode NVARCHAR(25))
INSERT INTO #ContractBU
SELECT N.value('.', 'NVARCHAR(25)') AS ContractBUCode FROM @XML.nodes('code') AS T(N)
---ContractSBU
SET @XML = CAST(('<code>'+REPLACE(@ContractSBU,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #ContractSBU (ContractSubBUCode NVARCHAR(25))
INSERT INTO #ContractSBU
SELECT N.value('.', 'NVARCHAR(25)') AS ContractSubBUCode FROM @XML.nodes('code') AS T(N)
---Sponsor
SET @XML = CAST(('<code>'+REPLACE(@Sponsor,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #Sponsor (SponsorCode NVARCHAR(25))
INSERT INTO #Sponsor
SELECT N.value('.', 'NVARCHAR(25)') AS SponsorCode FROM @XML.nodes('code') AS T(N)
---QuotedBY
SET @XML = CAST(('<code>'+REPLACE(@QuotedBy,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #QuotedBy (QuotedBy NVARCHAR(25))
INSERT INTO #QuotedBy
SELECT N.value('.', 'NVARCHAR(25)') AS QuotedBy FROM @XML.nodes('code') AS T(N)
select QuotedBy
into #temp1 from #QuotedBy where QuotedBy NOT like 'C-%'
select QuotedBy
into #temp2 from #QuotedBy where QuotedBy like 'C-%'
--ActivitySBU
SET @XML = CAST(('<code>'+REPLACE(@ActivitySBU,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #ActivitySBU (ActivitySBU NVARCHAR(25))
INSERT INTO #ActivitySBU
SELECT N.value('.', 'NVARCHAR(25)') AS ActivitySBU FROM @XML.nodes('code') AS T(N)
--ActivityIntercoSBU
SET @XML = CAST(('<code>'+REPLACE(@ActivityIntercoSBU,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #ActivityIntercoSBU (ActivityIntercoSBU NVARCHAR(25))
INSERT INTO #ActivityIntercoSBU
SELECT N.value('.', 'NVARCHAR(25)') AS ActivityIntercoSBU FROM @XML.nodes('code') AS T(N)
---ContractCode
SET @XML = CAST(('<code>'+REPLACE(@ContractCode,@Delimiter ,'</code><code>')+'</code>') AS XML)
CREATE TABLE #Contract (ContractCode NVARCHAR(25))
INSERT INTO #Contract
SELECT N.value('.', 'NVARCHAR(25)') AS ContractCode FROM @XML.nodes('code') AS T(N)
--- GROUP BY Activity
IF (@GroupByCPCode = 0)
BEGIN
SELECT A.activityCode AS ActivityCode,
AU.analyticalUnitName AS ActivitySBU,
SU.subUnitName AS ActivitySubUnit,
FCPS.financeContractPaymentScheduleCode AS CPCode,
FCPS.financeContractPaymentScheduleName AS CPTitle,
CAU.AnalyticalUnitName AS ContractOwnerSBU,
CL.clientName AS Sponsor,
FC.sponsorReference AS SponsorReference,
FC.comment AS ContractComments,
FCSAD.activityStartDate AS StartDate,
FCSAD.activityEndDate AS EndDate
INTO #MainTemp
FROM Local_FinanceContracts AS FC
INNER JOIN Local_FinanceContractsPaymentsSchedules AS FCPS ON FCPS.financeContractIncId = FC.financeContractIncId
AND FCPS.financeContractSqlId = FC.financeContractSqlId
AND FCPS.isDeleted = 0
INNER JOIN Local_FinanceContractsPaymentsSchedulesActivities AS FCPSA ON FCPSA.financeContractPaymentScheduleIncId = FCPS.financeContractPaymentScheduleIncId
AND FCPSA.financeContractPaymentScheduleSqlId = FCPS.financeContractPaymentScheduleSqlId
AND FCPSA.isDeleted = 0
INNER JOIN Activities AS A ON A.activityIncId = FCPSA.activityIncId
AND A.activitySqlId = FCPSA.activitySqlId
AND A.isDeleted = 0
INNER JOIN Local_FinanceContractStructureActivitiesDetails AS FCSAD ON FCSAD.activityIncId = A.activityIncId
AND FCSAD.activitySqlId = A.activitySqlId
AND FCSAD.isDeleted = 0
INNER JOIN AnalyticalUnits CAU ON FC.AnalyticalUnitIncId = CAU.analyticalUnitINcId
AND FC.AnalyticalUnitsqlId = CAU.AnalyticalUNitSqlId
AND CAU.Isdeleted = 0
INNER JOIN BusinessUnits CBU ON CAU.businessUnitIncId = CBU.businessUnitIncId
AND CAU.businessUnitSqlId = CBU.businessUnitSqlId
AND CBU.Isdeleted = 0
LEFT JOIN Local_FinanceContractStructureActivitiesDetailsAdditionalCosts AS FCAC ON FCAC.financeContractStructureActivityDetailIncId = FCSAD.financeContractStructureActivityDetailIncId
AND FCAC.financeContractStructureActivityDetailSqlId = FCSAD.financeContractStructureActivityDetailSqlId
AND FCAC.isDeleted = 0
LEFT JOIN AnalyticalUnits AS AU ON A.analyticalUnitWhoDoIncId = AU.analyticalUnitIncId
AND A.analyticalUnitWhoDoSqlId = AU.analyticalUnitSqlId
AND AU.isDeleted = 0
LEFT JOIN AnalyticalUnits AS SADAU ON FCSAD.intercoAnalyticalUnitIncId = SADAU.analyticalUnitIncId
AND FCSAD.intercoAnalyticalUnitSqlId = SADAU.analyticalUnitSqlId
AND SADAU.isDeleted = 0
LEFT JOIN SubUnits SU ON SU.subUnitIncId = A.subUnitWhoDoIncId
AND SU.subUnitSqlId = A.subUnitWhoDoSqlId
AND SU.isDeleted = 0x0
LEFT JOIN Clients AS CL ON FC.sponsorIncId = CL.clientIncId
AND FC.sponsorSqlId = CL.clientSqlId
AND CL.isDeleted = 0
LEFT JOIN Operators FCOP ON FCOP.operatorIncId = FC.financeQuotationCreatedByIncId
AND FCOP.operatorSqlId = FC.financeQuotationCreatedBySqlId
AND FCOP.isDeleted = 0
LEFT JOIN Contacts FCCT ON FCCT.contactIncId = FC.financeQuotationCreatedByContactIncId
AND FCCT.contactSqlId = FC.financeQuotationCreatedByContactSqlId
AND FCCT.isDeleted = 0
INNER JOIN #ContractBU ON CBU.businessUnitCode = #ContractBU.ContractBUCode
INNER JOIN #ContractSBU ON CAU.analyticalUnitCode = #ContractSBU.ContractSubBUCode
INNER JOIN #Sponsor ON CL.clientCode = #Sponsor.SponsorCode
INNER JOIN #ActivitySBU ON AU.analyticalUnitCode = #ActivitySBU.ActivitySBU
INNER JOIN #ActivityIntercoSBU ON SADAU.analyticalUnitCode = #ActivityIntercoSBU.ActivityIntercoSBU
INNER JOIN #Contract ON FC.financeContractCode = #Contract.ContractCode
left JOIN #temp1 ON FCOP.operatorCode = #temp1.QuotedBy
left JOIN #temp2 ON FCCT.contactCode = #temp2.QuotedBy
WHERE (FC.decisionDate between @DecisionDateFrom and @NewDecisionDateTo)
AND (FCSAD.activityStartDate between @ActivityStartDateFrom and @NewActivityStartDateTo)
AND (FCSAD.activityEndDate between @ActivityEndDateFrom and @NewActivityEndDateTo)
----select * from #MainTemp
SELECT
A.activitycode,
SUM(ISNULL(FCSAD.activityOwnerRevenue,0)) AS RevenueAllocated,
SUM(ISNULL(FCSAD.activityOwnerRevenue,0)-(ISNULL(FCSAD.absoluteDiscount,0)-(ISNULL(FCSAD.percentageDiscount,0)*(ISNULL(FCSAD.activityOwnerRevenue,0)/100)))) AS RevenueAllocatedWithDisc,
CASE WHEN CONVERT(BIT, FCAC.isInvoiceableToClient) = 1 THEN SUM(ISNULL(FCAC.maximumPossibleValue,0)) END AS CPMaxAddnlCostsIC,
CASE WHEN CONVERT(BIT, FCAC.isInvoiceableToClient) = 1 THEN SUM(ISNULL(FCAC.convertedAdditionalCostValuePaid,0)) END AS AddnlCostsPaidIC,
SUM(ISNULL(FCAC.convertedAdditionalCostValuePaid,0)) AS AddnlCostsICNotDefined,
CASE WHEN CONVERT(BIT, FCAC.isInvoiceableToClient) = 0 THEN SUM(ISNULL(FCAC.convertedAdditionalCostValuePaid,0)) END AS AddnlCostsNotIC
INTO #ADDCTemp
FROM Local_FinanceContracts AS FC
INNER JOIN Local_FinanceContractsPaymentsSchedules AS FCPS ON FCPS.financeContractIncId = FC.financeContractIncId
AND FCPS.financeContractSqlId = FC.financeContractSqlId
AND FCPS.isDeleted = 0
INNER JOIN Local_FinanceContractsPaymentsSchedulesActivities AS FCPSA ON FCPSA.financeContractPaymentScheduleIncId = FCPS.financeContractPaymentScheduleIncId
AND FCPSA.financeContractPaymentScheduleSqlId = FCPS.financeContractPaymentScheduleSqlId
AND FCPSA.isDeleted = 0
INNER JOIN Activities AS A ON A.activityIncId = FCPSA.activityIncId
AND A.activitySqlId = FCPSA.activitySqlId
AND A.isDeleted = 0
INNER JOIN Local_FinanceContractStructureActivitiesDetails AS FCSAD ON FCSAD.activityIncId = A.activityIncId
AND FCSAD.activitySqlId = A.activitySqlId
AND FCSAD.isDeleted = 0
INNER JOIN AnalyticalUnits CAU ON FC.AnalyticalUnitIncId = CAU.analyticalUnitINcId
AND FC.AnalyticalUnitsqlId = CAU.AnalyticalUNitSqlId
AND CAU.Isdeleted = 0
INNER JOIN BusinessUnits CBU ON CAU.businessUnitIncId = CBU.businessUnitIncId
AND CAU.businessUnitSqlId = CBU.businessUnitSqlId
AND CBU.Isdeleted = 0
LEFT JOIN Local_FinanceContractStructureActivitiesDetailsAdditionalCosts AS FCAC ON FCAC.financeContractStructureActivityDetailIncId = FCSAD.financeContractStructureActivityDetailIncId
AND FCAC.financeContractStructureActivityDetailSqlId = FCSAD.financeContractStructureActivityDetailSqlId
AND FCAC.isDeleted = 0
LEFT JOIN AnalyticalUnits AS AU ON A.analyticalUnitWhoDoIncId = AU.analyticalUnitIncId
AND A.analyticalUnitWhoDoSqlId = AU.analyticalUnitSqlId
AND AU.isDeleted = 0
LEFT JOIN AnalyticalUnits AS SADAU ON FCSAD.intercoAnalyticalUnitIncId = SADAU.analyticalUnitIncId
AND FCSAD.intercoAnalyticalUnitSqlId = SADAU.analyticalUnitSqlId
AND SADAU.isDeleted = 0
LEFT JOIN SubUnits SU ON SU.subUnitIncId = A.subUnitWhoDoIncId
AND SU.subUnitSqlId = A.subUnitWhoDoSqlId
AND SU.isDeleted = 0x0
LEFT JOIN Clients AS CL ON FC.sponsorIncId = CL.clientIncId
AND FC.sponsorSqlId = CL.clientSqlId
AND CL.isDeleted = 0
LEFT JOIN Operators FCOP ON FCOP.operatorIncId = FC.financeQuotationCreatedByIncId
AND FCOP.operatorSqlId = FC.financeQuotationCreatedBySqlId
AND FCOP.isDeleted = 0
LEFT JOIN Contacts FCCT ON FCCT.contactIncId = FC.financeQuotationCreatedByContactIncId
AND FCCT.contactSqlId = FC.financeQuotationCreatedByContactSqlId
AND FCCT.isDeleted = 0
INNER JOIN #ContractBU ON CBU.businessUnitCode = #ContractBU.ContractBUCode
INNER JOIN #ContractSBU ON CAU.analyticalUnitCode = #ContractSBU.ContractSubBUCode
INNER JOIN #Sponsor ON CL.clientCode = #Sponsor.SponsorCode
INNER JOIN #ActivitySBU ON AU.analyticalUnitCode = #ActivitySBU.ActivitySBU
INNER JOIN #ActivityIntercoSBU ON SADAU.analyticalUnitCode = #ActivityIntercoSBU.ActivityIntercoSBU
INNER JOIN #Contract ON FC.financeContractCode = #Contract.ContractCode
left JOIN #temp1 ON FCOP.operatorCode = #temp1.QuotedBy
left JOIN #temp2 ON FCCT.contactCode = #temp2.QuotedBy
WHERE (FC.decisionDate between @DecisionDateFrom and @NewDecisionDateTo)
AND (FCSAD.activityStartDate between @ActivityStartDateFrom and @NewActivityStartDateTo)
AND (FCSAD.activityEndDate between @ActivityEndDateFrom and @NewActivityEndDateTo)
GROUP BY A.activitycode,FCAC.isInvoiceableToClient
ORDER BY A.activitycode
----select * from #ADDCTemp
------Final SELECT ststement
SELECT DISTINCT
T1.ActivityCode,
T1.ActivitySBU,
T1.ActivitySubUnit,
T1.CPCode,
T1.CPTitle,
T1.ContractOwnerSBU,
T1.Sponsor,
T1.SponsorReference,
CAST(T1.ContractComments AS NVARCHAR(MAX)) AS ContractComments,
T1.StartDate,
T1.EndDate,
'' AS StatusCheck,
T2.RevenueAllocated,
T2.RevenueAllocatedWithDisc,
'' AS TotalInvoiceValue,
'' AS TotalInvoiceValueSentInv,
T2.CPMaxAddnlCostsIC,
T2.AddnlCostsPaidIC,
'' AS RevenueAddnlCostsIC,
'' AS RevenueAddnlCostsSentIC,
T2.AddnlCostsICNotDefined,
T2.AddnlCostsNotIC
FROM #MainTemp T1
left JOIN #ADDCTemp T2
ON T1.activitycode = T2.activityCode
ORDER BY T1.activitycode
DROP TABLE #MainTemp
DROP TABLE #ADDCTemp
END
END