Sum the value of the column by grouping monthly and by product

Question

Staff I need to calculate the monthly consumption of the licenses used here in the company I work.

I have a table with the product (dbo. Product), the number of licenses installed on the server (dbo. License) the date the product was installed (dbo.Firstdate) and the date of uninstallation (dbo.Decomdate), when the product is still installed the uninstall date has the NULL value.

Example table available on sqlfiddle

I would like to know how to add up the consumption of licenses by grouping by month and by product in the last 12 months.

Ex.:

Date | Product | Totallicense
2017-08 | Adobe Photoshop | 8
2017-08 | Coreldraw | 5
2017-09 |...
2018-07 | Adobe Photoshop | 4
2018-07 | Coreldraw | 2

Example table available on sqlfiddle

Answer 1

Test this script here and see if it works according to what you need

SELECT Product
      ,CAST(YEAR(FirstDate) AS CHAR(4)) 
       +RIGHT('0' + CAST(MONTH(FirstDate) AS VARCHAR(2)), 2) AS PERIODO
      ,SUM(License)                                          AS LICENSE
FROM Baseline
WHERE FirstDate >= DATEADD(MONTH, -12, GETDATE()) 
GROUP BY Product
        ,CAST(YEAR(FirstDate) AS CHAR(4)) 
         +RIGHT('0' + CAST(MONTH(FirstDate) AS VARCHAR(2)), 2)

To test in the example you provided sqlfiddle

Answer 2

The query you put in SQL Fiddle was almost complete, just needed to group by date and put the condition of 12 months:

SELECT      FirstDate       AS [Date]
        ,   Product         
        ,   SUM(License)    AS [TotalLicense]
FROM        Baseline
GROUP BY    FirstDate
        ,   Product
HAVING      FirstDate >= CAST(DATEADD(MONTH, -12, GETDATE()) AS DATE)
ORDER BY    FirstDate