SQL code to project how many clients will reach age 55 each month for a year

I am trying to get the number of clients who are currently >= age 55, which I’ve managed, but next I want to find out how many people will be >= 55 in a month’s time, then in 2 month’s time etc up to 12 months.

Here is what I have so far, but I’m not sure how to go about the next bit. Any help greatly appreciated! Thanks.

SELECT 
    SUM(CASE 
        WHEN [C].[BirthDate] > dateadd(year, -54, getdate()) AND [E].[MfChk4] = '0' 
        THEN 0 
        ELSE 1 
    END) AS [Age Eligible]
FROM 
    [dbo].[CR_Entity] [E]
    LEFT OUTER JOIN [dbo].[CR_RefMaster] [RM] 
        ON  [RM].[EntCode] = [E].[EntClient] 
        AND [RM].[Status] = '0'
    LEFT OUTER JOIN [dbo].[Compliance] [C] 
        ON  [C].[AddrCode] = [RM].[RefCode]

The result I want for the next bit will be something like…

Answer

You would have to write multiple case statements like:

SUM
(CASE WHEN [C].[BirthDate] > dateadd(month,-54*12+1, getdate()) 
    AND [E].[MfChk4] = '0' 
             THEN 0 ELSE 1 END) AS Month1,
SUM
(CASE WHEN [C].[BirthDate] > dateadd(month,-54*12+2, getdate()) 
    AND [E].[MfChk4] = '0' 
             THEN 0 ELSE 1 END) AS Month2,
.
.
.
SUM
(CASE WHEN [C].[BirthDate] > dateadd(month,-54*12+12, getdate()) 
    AND [E].[MfChk4] = '0' 
             THEN 0 ELSE 1 END) AS Month12