How to single out values in a database based on date being month-end?

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

You can use [EOMONTH](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017) to get the last day of the month since SQL Server 2012. EOMONTH accepts a months offset too — Panagiotis Kanavos, Mar 15 '19 at 17:32
Why isn't it as simple as changing `<= @Date` to `< @Date` and making `@Date` a `date` instead of a `datetime`? — Tab Alleman, Mar 15 '19 at 17:38
Add another field named MonthName, MonthName = name of the month with year , i.e January2010, February2010. and then group by MonthName as well. `Select MonthName =DATENAME(month, GETDATE()) + CAST( YEAR(GETDATE()) AS VARCHAR)` — Hasan Mahmood, Mar 15 '19 at 17:44
`DECLARE @Date DATETIME = Getdate() DECLARE @daycount INT = 90 DECLARE @startDate DATETIME = Dateadd(dd, @daycount*-1, @Date) SELECT sub.Instrument, MonthName = DATENAME(month, GETDATE()) + CAST( YEAR(GETDATE()) AS VARCHAR), ( Sum(sub.GrossExposure) / @daycount ) AS AvgGrossExposure FROM (SELECT DateField, Instrument, GrossExposure FROM table WHERE DateField <= @Date AND Datefield >= @startDate ) sub GROUP BY Instrument, DATENAME(month, GETDATE()) + CAST( YEAR(GETDATE()) AS VARCHAR)` — Hasan Mahmood, Mar 15 '19 at 17:46

Thermos · Accepted Answer · 2019-03-15T18:43:16.100

0

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

edited Mar 15 '19 at 18:43

answered Mar 15 '19 at 18:35

Thermos

181
6

For some reason I wasn't able to use "WITH" in my version of SQL, but I was able to utilize the EOMONTH(DateField) and then filtered the results with a statement "WHERE DateField = EndOfMonthDate" to get just data from month ends. Thanks! – An Nuyen Mar 19 '19 at 18:55
If the CTE isn't working out for you, you can just use that first SELECT statement as a sub-select in your main query, like this: ` SELECT CTE.Instrument, CTE.EndOfMonthDate, AVG(CTE.GrossExposure) AS AvgGrossExposure FROM ( SELECT EOMONTH(DateField) AS EndOfMonthDate ,DateField ,Instrument ,GrossExposure FROM TABLE WHERE DateField BETWEEN GETDATE()-90 AND GETDATE() ) CTE GROUP BY CTE.Instrument, CTE.EndOfMonthDate ` Glad you figured it out! – Thermos Mar 20 '19 at 13:16

How to single out values in a database based on date being month-end?

1 Answers1