;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
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
Please make an attempt to changed code below
;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.Allo
CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
,u.IsAllocatedByTransferTa
,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
,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 ,TT.IsNotActive
FROM CTE1 Z
LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name
)u WHERE u.rnk = 1 AND u.IsNotActive <> 'True'
)
,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