How to single out values in a database based on da

Question

I have a problem where I need to query a database which includes multiple lines of trade activity for the past 90 days. Currently the query is built to determine the average amount over the 90 day period - so each day has a single exposure value and the query helps us determine the average exposure over 90 days by just summing the daily values and then dividing by 90. And it does this as the date rolls forward, so the value is updated each day the query is run.

The above is simple enough to execute, but now I need to determine the average month-end amounts for the past 3 months. I've figured out how to pull just month-end dates, but not sure how to join that with the current query. Additionally, needs to be able to update itself rolling forward.

/* Test query below */ 

DECLARE @Date DATETIME = Getdate() 
DECLARE @daycount INT = 90
DECLARE @startDate DATETIME = Dateadd(dd, @daycount*-1, @Date)  

SELECT sub.Instrument, 
       ( Sum(sub.GrossExposure) / @daycount )    AS AvgGrossExposure
FROM   (SELECT DateField, 
               Instrument, 
               GrossExposure  
        FROM   table 
        WHERE  DateField <= @Date 
           AND Datefield >= @startDate 
        ) sub 
GROUP  BY Instrument

To calculate month-ends in the past 90 days, I've fiddled around with this, but it also includes today's date and I do not need that value in this case.

/* Test query for month-end dates, past 90 days */
DECLARE @Date DATETIME = GetDate()
DECLARE @daycount INT = 90
DECLARE @startDate DATETIME = Dateadd(dd, @daycount*-1, @Date) 

SELECT          max(datefield) AS month_ends
                FROM            table
                WHERE datefield <= @Date 
                AND datefield >= @startDate 
                GROUP BY        month(datefield), 
                                year(datefield)
                ORDER BY month_ends

Answer 1

Give this a try - you can use a common table expression to append the month end date of each DateField value using EOMONTH(DateField), and then use that in your GROUP BY, with the Average of all GrossExposure values that have that same EOMONTH value for each instrument.

WITH CTE AS (
    SELECT EOMONTH(DateField) AS EndOfMonthDate
        ,DateField
        ,Instrument
        ,GrossExposure
    FROM TABLE
    WHERE DateField BETWEEN GETDATE()-90 AND GETDATE()
)
SELECT CTE.Instrument,
    CTE.EndOfMonthDate,
    AVG(CTE.GrossExposure) AS AvgGrossExposure
FROM CTE
GROUP BY CTE.Instrument, CTE.EndOfMonthDate