;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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.