Query not to display StartDate depending on the 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, u.Comments,u.Principal
	FROM 
	(
		SELECT Z.Name,	Z.Allocation ,Z.allocated 	,  TT.Comments, TT.Principal,
		CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated'  or  TT.Comments = 'AllocationChanged'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'  or  TT.Comments = 'AllocationChanged' 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'  or  TT.Comments = 'AllocationChanged' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk 
		FROM CTE1 Z
		LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
	)u WHERE u.rnk = 1 
)
SELECT Name	,Allocation,	AllocStDate,	AllocEndDate	,  CASE WHEN Comments = 'Allocated' THEN Principal ELSE ClientName END  ClientName	
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
 end IsAllocatedByTransferTab
FROM CTE2
order by notallocated asc

Select allOpen in new window