SSMS: How to return only the value in the last row for each month/year instead of every row?

Question

The code I'm working from returns all dates within the month/year but I only want it to return the last date row instead of all daily rows.

Here is the code I'm working from.

;WITH OIL_INDEX AS (
    SELECT PRODUCT_SYMBOL
    ,CONTRACT_MONTH
    ,CONTRACT_YEAR
    ,CONTRACT_DETAIL
    ,TRADEDATE
    ,SETTLE
    ,AVG(SETTLE) OVER (
            PARTITION BY CONTRACT_DETAIL, YEAR(TRADEDATE), MONTH(TRADEDATE)
            ORDER BY TRADEDATE 
            ROWS BETWEEN 29 PRECEDING and CURRENT ROW
        ) AS MA30
    ,YEAR(TRADEDATE) AS TRADEYEAR
    ,MONTH(TRADEDATE) AS TRADEMONTH
    ,DAY(TRADEDATE) AS TRADEDAY
    ,row_number() OVER(
     PARTITION BY CONTRACT_DETAIL, TRADEDATE
      ORDER BY TRADEDATE DESC) AS RowNum
     FROM Pricing.dbo.MasterReport$

)

SELECT PRODUCT_SYMBOL
    ,CONTRACT_MONTH
    ,CONTRACT_YEAR
    ,CONTRACT_DETAIL
    ,TRADEDATE
    ,TRADEYEAR
    ,TRADEMONTH
    ,TRADEDAY
    ,SETTLE
    ,MA30
    FROM OIL_INDEX
    WHERE RowNum = 1 AND PRODUCT_SYMBOL
    IN ('CL','CY','WJ')
    ORDER BY PRODUCT_SYMBOL ASC, CONTRACT_DETAIL ASC,TRADEYEAR ASC, TRADEMONTH ASC, TRADEDAY ASC

Here is the result this gives.

Here is what I would like.

Answer 1

I think that you just need to modify the PARTITION BY clause of the window function:

Before:

ROW_NUMBER() OVER(
    PARTITION BY CONTRACT_DETAIL, TRADEDATE
    ORDER BY TRADEDATE DESC
) AS RowNum

After:

ROW_NUMBER() OVER(
    PARTITION BY CONTRACT_DETAIL, YEAR(TRADEDATE), MONTH(TRADEDATE)
    ORDER BY TRADEDATE DESC
) AS RowNum

Rationale: your partitioning clause creates a new group for each distinct (CONTRACT_DETAIL, TRADEDATE ) tuple; this is liley to generate a lot of groups, with potentially just one record per group. Instead, you want to partition by contract and month, and then rank records by trade date in each group.