Filtering Rows with Max Effective Date Using Conditional Aggregation in SQL

SQL Server Filtering on "as" Label Aliases: Best Practices and Techniques

How to Calculate the Sum of Case Statement with SUM() in SQL