TSQL Having clause not performing as expected with condition

DECLARE @ComparisonMonth DATE
SET @ComparsionMonth '09-01-2018'

SELECT 
    Date, Sales, CustomerID 
FROM
    Database1 t1
WHERE
    Date >= CASE 
               WHEN (SELECT MAX(Date) 
                     FROM Database1 t2 
                     INNER JOIN ON t1.PlayerID = t2.PlayerID) >= DATEADD(month, -4, @ComparisonMonth) 
                    AND (SELECT MAX(Date) 
                         FROM Database1 t2 
                         INNER JOIN ON t1.PlayerID = t2.PlayerID) < DATEADD(month, -1, @ComparsionMonth) 
                  THEN DATEADD(month, -4, @ComparisonMonth)
               WHEN (SELECT MAX(Date) 
                     FROM Database1 t2 
                     INNER JOIN ON t1.PlayerID = t2.PlayerID) >= DATEADD(month, -7, @ComparisonMonth) 
                    AND (SELECT MAX(Date) 
                         FROM Database1 t2 
                         INNER JOIN ON t1.PlayerID = t2.PlayerID) < DATEADD(month, -4, @ComparsionMonth) 
                  THEN DATEADD(month, -7, @ComparisonMonth)
            END
        AND Date < CASE 
                      WHEN (Select MAX(Date) from Database1 t2 INNER JOIN on t1.PlayerID=t2.PlayerID) >= DATEADD(month, -4, @ComparisonMonth) 
                           AND (Select MAX(Date) from Database1 t2 INNER JOIN on t1.PlayerID=t2.PlayerID) < DATEADD(month, -1, @ComparsionMonth)          
                         THEN DATEADD(month, -1, @ComparisonMonth)
                      WHEN (Select MAX(Date) from Database1 t2 INNER JOIN on t1.PlayerID=t2.PlayerID) >= DATEADD(month, -7, @ComparisonMonth) 
                           AND (Select MAX(Date) from Database1 t2 INNER JOIN on t1.PlayerID=t2.PlayerID) < DATEADD(month, -4, @ComparsionMonth) 
                         THEN DATEADD(month, -4, @ComparisonMonth)
                    END

    GROUP BY
        CustomerID, Date

Basically, I want to show only the values for a specific period depending on when the customer last purchased an item. For example, if a customer last purchased an item within the last 3 months I want to take all of their transactions from the last 3 months. Instead, i am getting the transactions from 6 months for the customer that last came in 3 months ago.

Thanks so much for the help and please let me know if you need any clarification.

Solution

My suggestion is to stop trying to achieve everything at once. Split your query to:

1 Find customers you need. Very simple query with precise filter condition:

DECLARE @Customers TABLE (CustomerID INT NOT NULL PRIMARY KEY, LastPurchase DATE, FirstTran DATE)

INSERT INTO @Customers(CustomerID, LastPurchase, FirstTran)
SELECT 
    CustomerID, MAX(Date), DATEADD(MM, -3, MAX(Date))
FROM Database1 d
WHERE d.date >= @ReportDate
GROUP BY CustomerID

2 Fetch their transactions. Now you have good filter (join predicate) again - CustomerID. Help server with filtering big table - find absolutely minimal date from collected data.

DECLARE @MinDate DATE

SELECT @MinDate = MIN(FirstTran)
FROM @Customers

SELECT d.*
FROM Database1 d
INNER JOIN @Customers c
on c.CustomerID = d.CustomerID
WHERE d.Date >= c.FirstTran
  AND d.Date >= @MinDate

Test your solution and if you will ever come up with some tiny single-step solution - refactor this code as you wish.