How to get the current month and previous months for the previous year

I have requirements to get the current month of the year and the last year’s month. Something like this:

Right now what I did is hard code it like this:

, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 1 THEN trans.OutQty END), 0.00) as 'Jan'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 2 THEN trans.OutQty END), 0.00) as 'Feb'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 3 THEN trans.OutQty END), 0.00) as 'Mar'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 4 THEN trans.OutQty END), 0.00) as 'Apr'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 5 THEN trans.OutQty END), 0.00) as 'May'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 6 THEN trans.OutQty END), 0.00) as 'Jun'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 7 THEN trans.OutQty END), 0.00) as 'Jul'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 8 THEN trans.OutQty END), 0.00) as 'Aug'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 9 THEN trans.OutQty END), 0.00) as 'Sep'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 10 THEN trans.OutQty END), 0.00) as 'Oct'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 11 THEN trans.OutQty END), 0.00) as 'Nov'
, ISNULL(SUM(CASE WHEN month(trans.DocDate) = 12 THEN trans.OutQty END), 0.00) as 'Dec'

Is it possible to do it dynamically instead of doing hard coded?

Answer

Please find below the answer on the MSSQL server as per your requirement.

 DECLARE @v_date DATETIME =GETDATE()    --'2019-11-11'

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 13
)
SELECT  
        LEFT(DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,@v_date)),3)+'-'+LEFT(DATENAME(YEAR,DATEADD(MONTH,-MonthNumber,@v_date)),10) AS [Month],
        MonthNumber+1 AS Month_number ,
        CASE WHEN CONCAT('Current Month - ' ,MonthNumber) = 'Current Month - 0' THEN 'Current Month' ELSE CONCAT('Current Month - ' ,MonthNumber) END AS Remark
FROM months;