Each month customers submit their data to our db. Each month my team analyzes the behavior of these customers for the last 39 months (for now that means 201911 - 202301). The problem is, some customers fail to submit their data by the 1st of the month. What I want to do is create a SQL query that pulls data for all the firms that HAVE submitted the most recent month (the max month). How would I make a query that does this and also only pulls the last 39 months (even though the data is there, I don't want to see customer info from, for example, 201909)?

This is the code I have so far. The issue with this code is that, in the WHERE section I have to manually add the minimum month (right now that is 201911), and I haven't figured out to pull data only for customers that have submitted for the most recent month (202301).

SELECT  
        prod.product AS 'product',
        cust.customer_NAME AS 'customer',
        ti.TITLE AS 'Title',
        tkpr.TIME_TYPE AS 'Time Type',
        tkpr.CONTRACT AS 'Contract',
        tkpr.YEAR AS 'Year',
        tkpr.YYYYMM 'Month (YYYYMM)',
        SUM(tkpr.FTE) AS 'Sum of FTE (RAW data)',
        SUM(tkpr.WORKED_AMOUNT_WP) AS 'Sum of Worked Amount Wp',    
        SUM(tkpr.WORKED_HOURS_WP) AS 'Sum of Worked Hours Wp'
FROM [REPORTS].[TKPR_SUMMARY_USD] AS tkpr
JOIN [REPORTS].[CUSTOMER] AS cust
ON tkpr.cust_ID = cust.cust_ID 
JOIN [EXTRACTS_CONFIGS].[ATS_PRRODUCT] AS prod
ON tkpr.product_ID = prod.product_ID
JOIN [EXTRACTS_CONFIGS].[ATS_TITLE] AS ti
ON tkpr.TITLE_ID = ti.TITLE_ID

WHERE   ti.TITLE_GROUP = 'US'
        AND prod.product_GROUP = 'US'
        AND tkpr.YYYYMM >= '201911'    
        AND tkpr.YYYYMM IN MAX(tkpr.YYYYMM)  
        AND cust.MAIN_OFFICE_COUNTRY = 'United States'
        AND cust.EXCLUDE_cust = 'F' 
        AND cust.IMPLEMENTATION_STATUS_ID = '3'

GROUP BY ti.TITLE, 
        prod.product,
        cust.customer_NAME,
        ti.TITLE,
        tkpr.TIME_TYPE,
        tkpr.CONTRACT,
        tkpr.YEAR,
        tkpr.YYYYMM
ORDER BY cust.customer_NAME ASC, tkpr.YYYYMM DESC

I tried to get the last 39 months worth of data for customers that have submitted data for the most recent month. I expected that I would get an error, but I can't think of any other way of doing this. It resulted in an error.