如何根据月末日期在数据库中选择值？ [英] How to single out values in a database based on date being month-end?

问题描述

我遇到一个问题，我需要查询一个包含过去90天多行贸易活动的数据库。当前，该查询的建立是为了确定90天期间的平均暴露量-因此每一天都有一个单一的暴露值，而该查询可以帮助我们确定90天的平均暴露量，方法是将每日值相加然后除以90。当日期向前滚动时会执行此操作，因此该值会在每天运行查询时更新。

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.

上面的代码很容易执行，但是现在我需要确定过去3个月的平均月末金额。我已经弄清楚了如何只提取月底日期，但是不确定如何将其与当前查询一起使用。此外，还需要能够向前更新。

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

要计算过去90天的月末，我已经花了很多时间，但是它也包含了今天的日期，在这种情况下，我不需要该值。

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

推荐答案

尝试一下-您可以使用公共表表达式，以使用EOMONTH（DateField）附加每个DateField值的月结束日期，然后在GROUP BY中使用该日期，并为每个工具的EOMONTH值具有相同的所有GrossExposure值的平均值。

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

这篇关于如何根据月末日期在数据库中选择值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！