CODEWITHSUNDEEP
sql-servert-sql
MilesToGoBeforeISleep
MilesToGoBeforeISleep

Reputation: 183

SQL Server 2012: How to calculate Quarterly Average with only values from the first of each month

Let's say I have the following table:

CREATE TABLE Portfolio.DailyNAV
(
Date date NOT NULL,
NAV int NOT NULL,
)
GO

The date column has the daily business day starting from '2015-02-02' and the NAV column has that day's total asset value. I want to get the average NAV for a quarter. For this example, let's assume I want to get it for the 2nd Quarter of 2016. My code is now:

Select AVG(NAV) As AvgNAV
FROM Portfolio.DailyNAV
WHERE year(Date) = '2016' AND DATEPART(QUARTER,Date) = '2'
GO

The problem I am facing is that this code calculates the daily average for the quarter but Average NAV should be calculated only using the first business date of each month for that quarter. So for 2016 Q2, the Average NAV should be the average from 2016-04-01, 2016-05-02 (the 1st was not a work day) and 2016-06-01. I don't want to simply change my WHERE clause and use those dates because at I want to make a stored procedure where the user can get the average NAV by putting in the Year and Quarter.

Thanks.

Upvotes: 1

Views: 1149

Answers (1)

Lamak
Lamak

Reputation: 70638

This should work:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY CONVERT(VARCHAR(6),[Date],112)
                                    ORDER BY [Date])
    FROM Portfolio.DailyNAV
    WHERE YEAR([Date]) = 2016 
    AND DATEPART(QUARTER,[Date]) = 2
    AND NAV IS NOT NULL
)
SELECT AVG(NAV) AvgNAV
FROM CTE
WHERE RN = 1;

Upvotes: 3

Related Questions