How to query for a value from the last month of each quarter?

Question

  SELECT YEAR(aum.AUM_Timeperiod) as Year,
         DATEPART(q, aum.AUM_TimePeriod) AS Quarter,
         SUM(cast(aum.AUM_AssetValue AS money)) as total_AssetValue
    FROM AssetUnderManagement as aum, 
         LineOfBusiness
   where aum.LOB_ID = LineOfBusiness.LOB_ID
     and LineOfBusiness.LOB_Name = 'Asset Management' 
GROUP BY YEAR(aum.AUM_Timeperiod), DATEPART(q, aum.AUM_TimePeriod);

The above query returns the value per quarter.

My question is how to change it if I want the Total_AssetValue of the last month in that quarter to be assigned to that quarter.

For eaxmple Quater 3 total_AssetValue is sum(100+200+300). but i want the quater 3 value to be 300 which is the last month value in that quater

Answer 1

How about:

SELECT
  Year,
  Quarter,
  (
    SELECT SUM(CAST(AUM_AssetValue AS MONEY)) 
      FROM AssetUnderManagement
     WHERE YEAR(AUM_Timeperiod) = i.Year
           AND MONTH(AUM_TimePeriod) = i.LastMonthInQuarter
  ) AS total_AssetValue
FROM
  (
  SELECT
    YEAR(aum.AUM_Timeperiod)        AS Year,
    DATEPART(q, aum.AUM_TimePeriod) AS Quarter,
    MAX(MONTH(aum.AUM_TimePeriod))  AS LastMonthInQuarter
  FROM
    AssetUnderManagement as aum, LineOfBusiness
  WHERE
    aum.LOB_ID = LineOfBusiness.LOB_ID
    AND LineOfBusiness.LOB_Name = 'Asset Management' 
  GROUP BY
    YEAR(aum.AUM_Timeperiod),
    DATEPART(q, aum.AUM_TimePeriod)
  ) AS i

Answer 2

Here's a version that uses a common table expression:

WITH MyData AS
    (SELECT YEAR(aum.AUM_Timeperiod) AS [Year], DATEPART(q, aum.AUM_TimePeriod) AS [Quarter],
        DATEPART(M, aum.AUM_TimePeriod) AS [Month],
        DATEPART(M, aum.AUM_TimePeriod) - (DATEPART(q, aum.AUM_TimePeriod) - 1) * 3 AS [MonthInQtr],
        SUM(CAST(aum.AUM_AssetValue AS MONEY)) AS [total_AssetValue]
    FROM AssetUnderManagement AS aum, LineOfBusiness
    WHERE aum.LOB_ID = LineOfBusiness.LOB_ID
        AND LineOfBusiness.LOB_Name = 'Asset Management'
    GROUP BY YEAR(aum.AUM_Timeperiod), DATEPART(q, aum.AUM_TimePeriod),
        DATEPART(M, aum.AUM_TimePeriod), DATEPART(M, aum.AUM_TimePeriod) -
        (DATEPART(q, aum.AUM_TimePeriod) - 1) * 3
    )

SELECT [Year], [Quarter]
FROM MyData
WHERE MonthInQtr = 3
ORDER BY [Year], [Quarter], [total_AssetValue]
;

You can use the same CTE from above and use the following query to get your original results

SELECT [Year], [Quarter], SUM([total_AssetValue])
FROM MyData
GROUP BY [Year], [Quarter]
ORDER BY [Year], [Quarter]
;