Monthly change query with SQL Server 2012

Question

I'm new to SQL and working on a monthly change calculation. I want to calculate the change between this months balance and the previous months balance.

This query is working for me but does not feel like the best syntax. Do you think this is fine, or are there any suggestions?

SELECT
 ROW_NUMBER() OVER(ORDER BY (CONVERT(varchar, effectivedate, 102)) ASC) Rn,
 CONVERT(varchar, effectivedate, 102) effectivedate,
 SUM(EuroCurrentBalance/1000000) TotalArrears,
 SUM(CASE WHEN Multiplier > 2 THEN EuroCurrentBalance/1000000 ELSE 0 END) NPLs,
 CASE WHEN ROW_NUMBER() OVER(ORDER BY (CONVERT(varchar, effectivedate, 102)) ASC) = 1 THEN 0 ELSE SUM(EuroCurrentBalance/1000000) - (LAG(SUM(EuroCurrentBalance/1000000),1,0) OVER (ORDER BY (CONVERT(varchar, effectivedate, 102)))) END MonthlyChange
 FROM
 DataWarehouse.dwa.FinanceMonthEndData
 GROUP BY
 CONVERT(varchar, effectivedate, 102)
 GO

• Simon Forsberg: Welcome to Code Review! To make life easier for reviewers, please add sufficient context to your question. "monthly change calculation" doesn't exactly say very much to me. The more you tell us about what your code does and what the purpose of doing that is, the easier it will be for reviewers to help you. See also this meta question

Answer 1

The size of the horizontal scrollbar is the first smell. I'd start by breaking down the CASE WHEN's into multiple lines:

SELECT
 ROW_NUMBER() OVER(ORDER BY (CONVERT(varchar, effectivedate, 102)) ASC) Rn,
 CONVERT(varchar, effectivedate, 102) effectivedate,
 SUM(EuroCurrentBalance/1000000) TotalArrears,
 SUM(CASE WHEN Multiplier > 2 THEN EuroCurrentBalance/1000000 
 ELSE 0 
 END) NPLs,
 CASE WHEN ROW_NUMBER() OVER(ORDER BY (CONVERT(varchar, effectivedate, 102)) ASC) = 1 THEN 0 
 ELSE SUM(EuroCurrentBalance/1000000) - (LAG(SUM(EuroCurrentBalance/1000000),1,0) OVER (ORDER BY (CONVERT(varchar, effectivedate, 102)))) 
 END MonthlyChange
FROM
 DataWarehouse.dwa.FinanceMonthEndData
GROUP BY
 effectivedate
GO

Note that you can group by effectivedate without doing the conversion again.

---

Then I'd look at the redundant pieces: EuroCurrentBalance/1000000 is repeated quite often, and CONVERT(varchar, effectivedate, 102) as well. Consider a subquery, or a CTE:

WITH PreSelectionCTE
AS
(
 SELECT 
 CONVERT(varchar, effectivedate, 102) EffectiveDate,
 EuroCurrentBalance/1000000 Arrear,
 CASE WHEN Multiplier > 2 THEN EuroCurrentBalance/1000000
 ELSE 0
 END NPL
 FROM
 DataWarehouse.dwa.FinanceMonthEndData
)
SELECT
 ROW_NUMBER() OVER(ORDER BY EffectiveDate) Rn,
 SUM(Arrear) TotalArrears,
 SUM(NPL) NPLs,
 CASE WHEN ROW_NUMBER() OVER(ORDER BY (EffectiveDate)) = 1 THEN 0
 ELSE SUM(Arrear) - (LAG(Arrear,1,0) OVER(ORDER BY EffectiveDate))
 END MonthlyChange
FROM
 PreSelectionCTE
GROUP BY
 EffectiveDate
GO

This could probably be improved further, but at least it's much easier to read now! Note that ASC is implicit in an ORDER BY - I'd simply omit it, or specify it consistently: you have it explicit in 2 of your 3 ORDER BY's.