If you absolutely need to return a formatted string then use the
FORMAT
function - it's been around since SQL Server 2012. Failing that use a combination of
REPLICATE
and
CONVERT
.
Speaking of
CONVERT
if you use
CONVERT(VARCHAR, CalendarYear )
rather than
CONVERT(CHAR, CalendarYear )
then you won't need the
LTRIM(RTRIM(
on the result. You didn't need the
LTRIM(RTRIM(
on the
CASE
section at all.
You also need to look up the use of
OVER
[
^] with Aggregate functions like
SUM
- It's a lot easier to use than awkward
GROUP BY
clauses like yours, the SQL is easier to read and it is also far more efficient.
Your whole query is over-complicated - there is no reason for the sub-query or changing aliases on columns. The whole thing appears to be as simple as the following (note - untested as you didn't give us any sample data)
SELECT PlanningResourceCenterSID, JDEAccountSID,
CarrierGroupSID, CurrencySID, CalendarMonthSID,
CalendarYear, CalendarMonthSID AS Calendar_Month_Local_Time,
FORMAT(CalendarMonthSID, '00') AS Month_C,
CONVERT(VARCHAR, CalendarYear ) + FORMAT(CalendarMonthSID, '00') AS Year_Month_con,
SUM(CostAmount) OVER (PARTITION BY PlanningResourceCenterSID, JDEAccountSID, CarrierGroupSID, CurrencySID, CalendarMonthSID, CalendarYear) AS CostAmount
FROM Cost_Fact
WHERE CurrencySID =3