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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
RIASAuthor Commented:
Will try and brb thanks
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.