Sql-server – Create a view showing total minutes per customer per month and change from previous month

aggregatesql serversql-server-2012

I have an existing database (SQL Server 2012, on premise installation) and need to add a column to a view which gets displayed via a Crystal Report.

The issue I am having is with restricting the scope of the minute sum to a single month and a single customer. Currently I am either having an error due to including a column in the select clause but not the group by clause or an aggregate function, or else the query will return the total minutes per customer as 'MinuteTotal' across all months in the referenced table.

My question is, how can I restrict this query in order to get the total minutes per CustomerName and per Month, so that I can compute the difference from month to month?

USE Masscomm_XT; 

WITH C0 AS (
SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate  
FROM dbo.reptview_LDUsageTrend
),

C1 AS (
SELECT CustomerName ,SUM(minutes)OVER(PARTITION BY CustomerName ,PostingMonth ,PostingYear) AS MinuteTotal
FROM C0
)

SELECT C0.CustomerName ,C1.MinuteTotal ,C0.PostingMonth ,C0.PostingYear
FROM C1
JOIN C0 ON C0.CustomerName = C1.CustomerName
;

QueryOutput

Best Answer

Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month. 

USE Masscomm_XT; 

WITH C0 AS (
  SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
  FROM dbo.reptview_LDUsageTrend
)

SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (Minutes) AS MinuteTotal
FROM C0
GROUP BY C0.CustomerName, C0.PostingYear, C0.PostingMonth
;
Related Question