List count for last 12 months broken down by month

I have a query that can get a total active count of products until a specified date @POINT

SELECT
    COUNT(DISTINCT e.productId) CNT
FROM
    pro p
    OUTER APPLY (
        SELECT
            TOP 1 p2.*
        FROM
            pro p2
        WHERE
            p2.productId = p.productId
            AND p2.date >= @POINT
            AND p2.STATUS IN ('SOLD', 'ACTIVE')
        ORDER BY
            p2.date ASC
    ) NEXT
WHERE
    p.date < @POINT
    AND p.STATUS = 'SOLD'
    AND NEXT.productId IS NOT NULL

Output for @POINT "01/01/2021" is

CNT
500

From a table like

productId date       STATUS
1001      01/04/2021 ACTIVE
1002      01/06/2021 SOLD
1003      01/07/2021 OTHER
...

How would I remake this query so that I can have a list of points (last 12 months) like

POINT       CNT
02/01/2021  550
01/01/2021  500
12/01/2020  450
...
03/01/2020  550

in one query? I don’t want to create a separate table of dates. The database is MSSQL.

---

Since no responded to question, I’ll assume there isn’t a function to generate these dates efficiently. I wrote a subquery that CAST couple dates to varchar to date, resulting with first months for the past 12 months.

Answer

Just group it by the Point and COUNT(*) the result.

You say in your update “CAST couple dates to varchar to date”, which I think means you want just the date part, in which case you can use CAST(NEXT.date AS date):

SELECT
    NEXT.date POINT,    -- or CAST(NEXT.date AS date)
    COUNT(*) CNT
FROM
    pro p
    OUTER APPLY (
        SELECT
            TOP 1 p2.*
        FROM
            pro p2
        WHERE
            p2.productId = p.productId
            AND p2.date >= @POINT
            AND p2.STATUS IN ('SOLD', 'ACTIVE')
        ORDER BY
            p2.date ASC
    ) NEXT
WHERE
    p.date < @POINT
    AND p.STATUS = 'SOLD'
    AND NEXT.productId IS NOT NULL
GROUP BY
    NEXT.date;    -- or CAST(NEXT.date AS date)