CODEWITHSUNDEEP
sql-server
pbickford
pbickford

Reputation: 89

Returning Sums of monthly daily averages with current month

I'm wondering if it's possible to construct a T-SQL query which returns both total monthly and the daily average sales--including the proper average of the current month.

The closest I've gotten is this:

SELECT CONVERT(CHAR(7), salesdate, 120) as [Month], 
 SUM(SalesPrice) as [Totals], 
 Max(DAY(EOMONTH(salesdate))), 
 SUM(SalesPrice)/Max(DAY(EOMONTH(salesdate))) 
FROM Sales
WHERE salesdate >= '1/1/2014' 
GROUP BY CONVERT(CHAR(7), salesdate, 120) 
ORDER BY [Month]

Which returns everything fine--except for the current month's daily average, since the formula I'm using to figure out the daily average (sum of the monthly sales/the # of days in that month) is overstating the number of sales days for the current month (unless it's run on the very last day of the month).

Obviously, this could get pieced together as a separate query, but does anyone have the necessary T-SQL mojo to suggest how it might work as a single query?

Upvotes: 0

Views: 89

Answers (1)

M.Ali
M.Ali

Reputation: 69524

SELECT CONVERT(CHAR(7), salesdate, 120) as [Month]
     ,SUM(SalesPrice) as [Totals]
     ,Max(DAY(CASE WHEN (MONTH(salesdate) = MONTH(GETDATE())) 
                                     AND (YEAR(salesdate) = YEAR(GETDATE())) 
                             THEN GETDATE() 
                         ELSE EOMONTH(salesdate) END))
     ,SUM(SalesPrice)/Max(DAY( CASE WHEN (MONTH(salesdate) = MONTH(GETDATE())) 
                                     AND (YEAR(salesdate) = YEAR(GETDATE())) 
                             THEN GETDATE() 
                         ELSE EOMONTH(salesdate) END)) 
FROM Sales
WHERE salesdate >= '20140101'  --<-- Use ANSI DATE format YYYYMMDD 
GROUP BY CONVERT(CHAR(7), salesdate, 120) 
ORDER BY [Month]

Upvotes: 1

Related Questions