1
\$\begingroup\$
SELECT SUM(CD.[Value]) AS [Value]
    FROM [{schema}].[CompanyData] CD
        INNER JOIN #Locations T
            ON T.LocationId = CD.CompanyId
        INNER JOIN (
            SELECT CDM.CompanyId, MAX(CDM.[Date]) as MaxDate
            FROM [{schema}].[CompanyData] CDM
                INNER JOIN #Locations T
                    ON T.LocationId = CDM.CompanyId
            GROUP BY CDM.CompanyId
        ) TM 
            ON CD.CompanyId = TM.CompanyId AND CD.[Date] = TM.MaxDate

I have query to select SUM from latest values for each row with different CompanyId.

Is this double join of same tables (Query and subQuery) fine or I can improve somehow it?

Thanks.

\$\endgroup\$
1
\$\begingroup\$

The query you showed is not running as-is, where's this CFID alias coming from?

As SQL Server supports Standard SQL's Analytical Functions you might rewrite MAX/Subquery using RANK (or ROW_NUMBER):

SELECT ...
FROM
  ( SELECT CDM.CompanyId, 
           any additonal columns you need,
           RANK() OVER (PARTITION BY CDM.CompanyId
                        ORDER BY CDM.[Date] DESC) AS rnk
        FROM [{schema}].[CompanyData] CDM
            INNER JOIN #Locations T
                ON T.LocationId = CDM.CompanyId
  ) TM
WHERE rnk = 1
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.