CODEWITHSUNDEEP
mysqlsqlsql-serverreporting-services
Rociboci
Rociboci

Reputation: 9

Obtain latest data for each product

I have a table that collects securities holdings data for Funds like below.

How do I extract latest data for each FundName?

EffectiveDate FundName SecurityName PercentageOfAssets
30/06/2022 Fund A xxx 33.33%
30/06/2022 Fund A yyy 33.33%
30/06/2022 Fund A zzz 33.33%
31/07/2022 Fund B xxx 50%
31/07/2022 Fund B yyy 50%
31/08/2022 Fund B yyy 50%
31/08/2022 Fund B zzz 50%
31/07/2022 Fund A xxx 50%
31/07/2022 Fund A yyy 50%

What I'm expecting

EffectiveDate FundName SecurityName PercentageOfAssets
31/07/2022 Fund A xxx 50%
31/07/2022 Fund A yyy 50%
31/08/2022 Fund B yyy 50%
31/08/2022 Fund B zzz 50%

Upvotes: 0

Views: 45

Answers (3)

griv
griv

Reputation: 2245

Create a subquery that will get the MAX EffectiveDate then JOIN it to an outer query to return the rest of your rows:

SELECT b.MaxDate, a.FundName, a.SecurityName, a.PercentageOfAssets
FROM (
      SELECT FundName, MAX(EffectiveDate) as MaxDate
      FROM Funds
      GROUP BY FundName
) b
INNER JOIN Funds a
ON a.FundName = b.FundName
  AND a.EffectiveDate = b.MaxDate
ORDER BY a.FundName, a.SecurityName ASC

Result:

| MaxDate    | FundName | SecurityName | PercentageOfAssets |
|------------|----------|--------------|--------------------|
| 31/07/2022 | Fund A   | xxx          | 50%                |
| 31/07/2022 | Fund A   | yyy          | 50%                |
| 31/08/2022 | Fund B   | yyy          | 50%                |
| 31/08/2022 | Fund B   | zzz          | 50%                |

Fiddle here.

Note: This will work for both MySQL and SQL Server.

Upvotes: 0

tftoclik
tftoclik

Reputation: 146

You can do it with cross apply in SQL Server:

select t1.EffectiveDate, t1.FundName, t1.SecurityName, t1.PercentageOfAssets
from @tbl t1
cross apply (
    select t2.FundName, max(t2.EffectiveDate) maxEffDate
    from @tbl t2
    group by t2.FundName
) latest
where latest.FundName = t1.FundName
    and latest.maxEffDate = t1.EffectiveDate
order by t1.EffectiveDate

Upvotes: 0

Srinivasan Rajasekaran
Srinivasan Rajasekaran

Reputation: 585

This will get the expected output

;WITH CTE AS (    
    SELECT EffectiveDate, FundName, SecurityName, PercentageOfAssets
        , ROW_NUMBER() OVER (PARTITION BY FundName ORDER BY EffectiveDate DESC) AS RowNum
)    
SELECT *
FROM CTE
WHERE RowNum = 1

Upvotes: 2

Related Questions