Sql Query with a condition

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT   
                   Z.Name ,
                   Z.Allocation, 
                    Z.Allocated ,
                   Z.AllocStDate ,
                   Z.AllocEndDate ,
                   Z.ClientName ,
                   Z.ID ,
                   Z.NotAllocated ,                                               
                   IIF( 1=1  
                   AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
                   , 'Allocated', '') AS IsAllocatedByTransferTab
      FROM     Z
                   
)
,CTE2 AS
(
      SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate      , u.ClientName ,u.ID,u.NotAllocated,
      CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
      ,u.IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM 
      (
            SELECT Z.Name,      Z.Allocation ,Z.allocated       ,  
            CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
             ,      Z.AllocEndDate      , Z.ClientName ,      Z.ID , 
             CASE WHEN TT.Comments = 'Allocated' THEN NULL ELSE Z.NotAllocated END      NotAllocated 
            , CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.Comments
            ELSE Z.IsAllocatedByTransferTab END IsAllocatedByTransferTab
            ,COUNT(*) OVER (PARTITION BY Z.Name ORDER BY CASE 
            WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk
                  ,TT.TrDate,TT.Comments 
            FROM CTE1 Z
            LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
      )u WHERE u.rnk = 1 
)
,CTE3 AS
(
      SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
      ,ID
      ,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
      ,IIF ( AllocEndDate < GETDATE()  , NULL  , Allocated  ) Allocated
      ,IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM CTE2
)
SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
,ID 
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , NULL , NotAllocated  ) NotAllocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Yes'  , Allocated  ) Allocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Allocated' , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
FROM CTE3
order by notallocated asc