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
;