Here is the query after some formatting.
IF @PeriodSelected IS NULL
AND @ExpenseType = 'Non Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID <= @PeriodEnd
AND NL.PERIODID >= @PeriodStart
AND (
EC.EXPENSEGROUP NOT IN ('2-###-##')
AND EC.EXPENSECODE NOT IN (
'8-010-02'
,'8-010-01'
)
)
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
IF @PeriodSelected IS NOT NULL
AND @ExpenseType = 'Non Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID = @PeriodSelected
AND (
EC.EXPENSEGROUP NOT IN ('2-###-##')
AND EC.EXPENSECODE NOT IN (
'8-010-02'
,'8-010-01'
)
)
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
IF @PeriodSelected IS NULL
AND @ExpenseType = 'Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID <= @PeriodEnd
AND NL.PERIODID >= @PeriodStart
AND (EC.EXPENSEGROUP IN ('2-###-##'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
IF @PeriodSelected IS NOT NULL
AND @ExpenseType = 'Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID = @PeriodSelected
AND (EC.EXPENSEGROUP IN ('2-###-##'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
What you have here is a stored proc with multiple execution paths. Read this article from Gail that explains this situation and a great way to deal with the sometime performance issues you are experiencing.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/