I'm sure there is. Look up the formatting parameters for the CONVERT function. You can use the technique of converting the result to a shortened CHAR value whenever the results of the CONVERT function are consistent in the length of their output.
I understand you desire to use three character month names to make the result more easily readable by carbon based processors, but formatting style "120" is more universally recognized by silicon based processors. Personally, I would refer any more complex formatting to the user interface.
The other big advantage of style "120" is that the result: YYYY-MM-DD sorts correctly even as a string.
If it's not practically useful, then it's practically useless.
actually the reason I like the 3 char month is because you can never rely on people to set up servers or accounts with the correct language settings.... and depending on what settings you have 01-04-2004 could be recorded in the system as 1 April or 4 Jan where as with either set up 1-Apr-2004 is always 1 April.
The database will then hold the value in whatever format it likes and reformating it on the way out is very much the role of whatever interface you want to implement.
Coalesce(a.code, b.code) AS code
, (SELECT Sum(c.amount1)
FROM tableA AS c
WHERE c.code = Coalesce(a.code, b.code)
AND c.[date] = DateAdd(year, -1, Coalesce(a.[date], b.[date])) AS amount1_last_year
, (SELECT Sum(d.amount1)
FROM tableA AS d
WHERE d.code = Coalesce(a.code, b.code)
AND d.date BETWEEN @dFirst AND
Coalesce(a.[date], b.[date])) AS amount1_mtd
FROM tableA AS a
FULL OUTER JOIN tableB AS b
ON (b.code = a.code
AND b.[date] = a.[date])
That's only part of the solution, but let's see if that part does what you want before we get too crazy!