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

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
;
  • 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? – rideoutcolin Jul 8 '16 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. – Terminus Jul 8 '16 at 3:10
  • 1
    Well, not manually add the minutes together; I'd use a sum. I'm not a masochist... – rideoutcolin Jul 8 '16 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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