0

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

1 Answer 1

1

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
;
3
  • Appears to have worked. Out of curiosity (and perhaps this should be a separate question) but is there a more efficient method of double checking that the selection is correct than by comparing a given month in the results against a query to select dates between the start and end of that month over a particular customer? Jul 8, 2016 at 3:05
  • Select the dates between the start and end date of that month for a particular customer and the manually adding the minutes together? There's no way that I know. But, I would trust the results of the query after I checked it the first time.
    – user98890
    Jul 8, 2016 at 3:10
  • 1
    Well, not manually add the minutes together; I'd use a sum. I'm not a masochist... Jul 8, 2016 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.