How do make a SQL hierarchy that can reference prior data entry in single SQL subquery

How do I create an SQL that can use a hierarchy of data (via pivot data) and build a sub-query that can reference back to a prior record, matching on a id. The id can have one or more assessment type(s), I need to retrieve all assessment_type that have no NO exiting "6-month reassessment" record. If the same id has another reference to, say, an "6-month assessment" then skip. I only need the id row that does not have a prior record(s) in the assessment_type field.

The data example is below: id, date_of_assessment, assessment_type. The SQL that created this data layout is inside the code example.

id     date-of_assessment assessment_type
1      2019-09-06         Initial
2      2019-09-06         Initial
1      2017-06-23         6-Month Reassessment
2      2017-09-01         6-Month Reassessment
3      2017-09-09         Initial

SELECT a.patid as id, date_of_assessment, 'Initial' assessment_type
  FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = 
 a.patid
  WHERE Type_Assessment_Value = 'Initial'
UNION ALL
SELECT a.patid as id, date_of_assessment, '6-Month Reassessment' 
assessment_type
 FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = 
a.patid
  WHERE Type_Assessment_Value = '6-Month Reassessment'
UNION ALL
    SELECT a.patid as id, date_of_assessment, 'Transition/Discharge' 
assessment_type
  FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = 
a.patid
  WHERE Type_Assessment_Value = 'Transition/Discharge'

Expected result: all others that have a prior or after (date wise) will be excluded.

id     date-of_assessment assessment_type
3      2017-09-09         Initial

You may try this. As the result shown in above query, you are getting some part of your result from above query, just there are some extra records are coming in the result.

And you stated that "Expected result: all others that have a prior or after (date wise) will be excluded.". so you need a filter to exclude all of the dates less then the last 6-Month Reassessment record. So we group your record in a subquery and filter the record on behalf of date_of_assessment as follows.

SELECT  id, date_of_assessment, assessment_type FROM (
    SELECT a.patid as id, date_of_assessment, 'Initial' assessment_type
    FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = a.patid
    WHERE Type_Assessment_Value = 'Initial'
    UNION ALL
    SELECT a.patid as id, date_of_assessment, '6-Month Reassessment' assessment_type
    FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = a.patid
    WHERE Type_Assessment_Value = '6-Month Reassessment'
    UNION ALL
    SELECT a.patid as id, date_of_assessment, 'Transition/Discharge' assessment_type
    FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = a.patid
    WHERE Type_Assessment_Value = 'Transition/Discharge'
) AS TAB 
WHERE TAB.Id NOT IN (SELECT a.patid FROM cw_cans_assessment a 
                     INNER JOIN DCFS.CANS c ON c.patid = a.patid
                     GROUP BY a.patid, Type_Assessment_Value HAVING COUNT(a.patid)>1 )

EDIT:- As you said that you want to filter all the records whose total count is less then 2, so we filter the records on basis of count of individual id, you may change group by in last to apply more filter.