SQL query with a condition

Hello, I have perfectly working query
Need a condition in there. Any suggestions are welcome
;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
	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 
		FROM CTE1 Z
		LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
	)u WHERE u.rnk = 1 
)
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
FROM CTE2
order by notallocated asc

Open in new window


Please find the excel sheet for the query.

Thanks
ExpectedResult5.xlsx
RIASAsked:
Who is Participating?
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Rias

Please make an attempt to below changed code

;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
0
 
Ganesh GuruduSenior ConsultantCommented:
try this code. modified something

		
		
		;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, 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 
		FROM CTE1 Z
		LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
	)u WHERE u.rnk = 1 
)
SELECT Name	,Allocation,	AllocStDate,	AllocEndDate	,ClientName	
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,IIF ( AllocStDate > AllocEndDate AND TT.Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND TT.Comments='Allocated' then 'Allocated' else  
 IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
 end IsAllocatedByTransferTab
FROM CTE2
order by notallocated asc

Open in new window

1
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Rias

;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
      ,TT.TrDate,TT.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
            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
1
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RIASAuthor Commented:
Will try and brb thanks
0
 
RIASAuthor Commented:
Hello Vaibhav,
Got an error
Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.TrDate" could not be bound.
Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.Comments" could not be bound.
0
 
RIASAuthor Commented:
Hello Ganesh,
Got an error:
Msg 207, Level 16, State 1, Line 59
Invalid column name 'Comments'.
0
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Rias

Please make an attempt to below changed code

;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
      ,TT.TrDate,TT.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

Vaibhav
1
 
RIASAuthor Commented:
Thanks...trying
0
 
RIASAuthor Commented:
Got an error:

Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.TrDate" could not be bound.
Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.Comments" could not be bound.
0
 
Ganesh GuruduSenior ConsultantCommented:
Check this Query

		
		
		;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
	FROM 
	(
		SELECT Z.Name,	Z.Allocation ,Z.allocated 	,  TT.Comments,
		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 
		FROM CTE1 Z
		LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
	)u WHERE u.rnk = 1 
)
SELECT Name	,Allocation,	AllocStDate,	AllocEndDate	,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

Open in new window

1
 
RIASAuthor Commented:
Thanks!
0
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Please select the answer as solution if you problem is solved
1
 
RIASAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.