Return Bit Value based on previous records by Category
Here is one approach (but please, stop changing your requirements):

DECLARE @x TABLE(MAKEID INT, FAILURES varchar(5), CREATEDATE date);

INSERT @x VALUES
(101,'TRUE ','20151201'),    (101,'FALSE','20151205'),    (101,'TRUE ','20151206'),
(101,'FALSE','20151208'),    (101,'TRUE ','20151210'),    (101,'FALSE','20151215'),
(101,'FALSE','20160101'),    (101,'FALSE','20160102'),    (102,'TRUE ','20151202'),
(102,'TRUE ','20151204'),    (102,'FALSE','20151205'),    (102,'TRUE ','20151206'),
(102,'FALSE','20151207'),    (102,'TRUE ','20160101');

;WITH x AS
(
  SELECT MAKEID, FAILURES,
    -- allow us to identify the last 6 rows:
    rn = ROW_NUMBER() OVER (PARTITION BY MAKEID ORDER BY CREATEDATE DESC)
  FROM @x
)
SELECT MAKEID, FOUROFLASTSIXFAILED = CASE 
  -- make sure there have been at least 6 tests - though this may change:
    WHEN COUNT(*) &gt;= 6 
  -- make sure *at least* 4 of those have failed *within the last 6*
  -- though this may change (again), too:
    AND COUNT(CASE WHEN rn &lt;= 6 AND FAILURES = 'TRUE' THEN 1 END) &gt;= 4 
  THEN 'TRUE' ELSE 'FALSE' END 
FROM x 
GROUP BY MAKEID
ORDER BY MAKEID;


Results (I think these are right, as opposed to the desired output stated in your question, which seem to conflict with your stated requirements):

MAKEID    FOUROFLASTSIXFAILED
------    -------------------
101       FALSE
102       TRUE


It would seem to me that MAKEID = 102 is the only one that has failed exactly four times out of its last six entries, unless the FAILURES column in the original table really represents NOT_FAILURES.