Add column value by grouping monthly and by product

Question

Add column value by grouping monthly and by product

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

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

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 uninstall date , when the product is still installed the uninstall date is NULL.

Sample table available in sqlfiddle

I would like to know how to add license consumption 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

Sample table available in sqlfiddle

sql sql-server sum

asked by anonymous 29.08.2018 / 16:08

2 answers

Error generating pdf with dompdf Datepicker jQuery need to pass a parameter from the Java controller

score 0 · Answer 1

The query that you put in SQL Fiddle was almost complete, you just needed to group by date and put the condition of the 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

score 0 · Answer 2

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 the example you have made sqlfiddle