Sql query with join

First of all: Always use alias names.

Then: either use CTE's or sub-queries. I don't like mixing it.

E.g.

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     JOBINPROGRESS 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   JOBINPROGRESS 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 )
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
ORDER BY Allocated DESC;

Select allOpen in new window

And check whether this is correct:

IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,

Select allOpen in new window

Cause NotAllocated being NULL does not mean it is not allocated. It means that we don't know whether it is allocated. SQL has a tri-state logic here.

Imho

IIF(T.OnJob IS NULL, 'YES', 'NO') AS NotAllocated ,

Select allOpen in new window

is a better solution.

And thinking positive is the best:

IIF(T.OnJob IS NULL, 'NO', 'YES') AS IsAllocated ,

Select allOpen in new window

How is your Transtable1 related to the others?

How this table (Transtable1) is joined with others?

Hello,
This is not yet joined but need to join it in the query

which column from the Transtable1 is joined with other table's column

Common columns are Name

Table: Transtable1
Column: Comments
Column : Name

Please see if this works.

WITH CTE_Drivers AS
(
	SELECT DriverID, MAX(CollectionDatetime) LastAllocStDate
	FROM JOBINPROGRESS
	GROUP BY DriverID
)
,CTE1
AS
(
SELECT DISTINCT Name,  Allocation, AllocStDate, AllocEndDate,
	CASE
		WHEN OnJob IS NULL THEN NULL
		ELSE ClientName 
	END ClientName, 
	CASE
		WHEN OnJob IS NULL THEN NULL
		ELSE ID 
	END ID, 
	CASE 
		WHEN OnJob IS NULL THEN 'YES'
		ELSE NULL
	END as NotAllocated,
	OnJob as Allocated
FROM 
	(SELECT B.NAME, A.Allocation,A.AllocStDate,A.AllocEndDate,A.ClientName,A.ID,
       CASE 
             WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate  > = CAST(GETDATE() AS date) THEN 'Allocated'
             ELSE NULL
       END as OnJob
	FROM (SELECT j.ID, j.ClientName, j.DriverID, CAST(j.CollectionDateTime  AS date) AllocStDate, j.Allocation, CAST(j.AllocEndDate AS date) AllocEndDate
	FROM JOBINPROGRESS j	
	INNER JOIN CTE_Drivers d ON j.driverId = d.driverId AND j.CollectionDatetime = d.LastAllocStDate) A
	INNER JOIN FieldResource B ON A.DriverID = B.ID) t order by Allocated desc
)
SELECT Name,  Allocation, AllocStDate, AllocEndDate,ClientName,ID,NotAllocated, CASE WHEN T.Comments IS NULL THEN 'Unallocated' else 'Allocated' allocated
FROM CTE1 c
LEFT JOIN Transtable1 T on c.Allocated = T.Comments

Select allOpen in new window

Will try

Msg 1033, Level 15, State 1, Line 34
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'allocated'.

sorry. updated.

WITH CTE_Drivers AS
(
	SELECT DriverID, MAX(CollectionDatetime) LastAllocStDate
	FROM JOBINPROGRESS
	GROUP BY DriverID
)
,CTE1
AS
(
SELECT DISTINCT Name,  Allocation, AllocStDate, AllocEndDate,
	CASE
		WHEN OnJob IS NULL THEN NULL
		ELSE ClientName 
	END ClientName, 
	CASE
		WHEN OnJob IS NULL THEN NULL
		ELSE ID 
	END ID, 
	CASE 
		WHEN OnJob IS NULL THEN 'YES'
		ELSE NULL
	END as NotAllocated,
	OnJob as Allocated
FROM 
	(SELECT B.NAME, A.Allocation,A.AllocStDate,A.AllocEndDate,A.ClientName,A.ID,
       CASE 
             WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate  > = CAST(GETDATE() AS date) THEN 'Allocated'
             ELSE NULL
       END as OnJob
	FROM (SELECT j.ID, j.ClientName, j.DriverID, CAST(j.CollectionDateTime  AS date) AllocStDate, j.Allocation, CAST(j.AllocEndDate AS date) AllocEndDate
	FROM JOBINPROGRESS j	
	INNER JOIN CTE_Drivers d ON j.driverId = d.driverId AND j.CollectionDatetime = d.LastAllocStDate) A
	INNER JOIN FieldResource B ON A.DriverID = B.ID) t 
)
SELECT Name,  Allocation, AllocStDate, AllocEndDate,ClientName,ID,NotAllocated, CASE WHEN T.Comments IS NULL THEN 'Unallocated' else 'Allocated' allocated
FROM CTE1 c
LEFT JOIN Transtable1 T on c.Allocated = T.Comments

Select allOpen in new window

Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'allocated'.

SELECT Name,  Allocation, AllocStDate, AllocEndDate,ClientName,ID,NotAllocated, CASE WHEN T.Comments IS NULL THEN 'Unallocated' else 'Allocated' allocated

Missed END. Updated.

WITH CTE_Drivers AS
(
	SELECT DriverID, MAX(CollectionDatetime) LastAllocStDate
	FROM JOBINPROGRESS
	GROUP BY DriverID
)
,CTE1
AS
(
	SELECT DISTINCT Name,  Allocation, AllocStDate, AllocEndDate,
		CASE
			WHEN OnJob IS NULL THEN NULL
			ELSE ClientName 
		END ClientName, 
		CASE
			WHEN OnJob IS NULL THEN NULL
			ELSE ID 
		END ID, 
		CASE 
			WHEN OnJob IS NULL THEN 'YES'
			ELSE NULL
		END as NotAllocated,
		OnJob as Allocated
	FROM 
		(SELECT B.NAME, A.Allocation,A.AllocStDate,A.AllocEndDate,A.ClientName,A.ID,
		   CASE 
				 WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate  > = CAST(GETDATE() AS date) THEN 'Allocated'
				 ELSE NULL
		   END as OnJob
		FROM (SELECT j.ID, j.ClientName, j.DriverID, CAST(j.CollectionDateTime  AS date) AllocStDate, j.Allocation, CAST(j.AllocEndDate AS date) AllocEndDate
		FROM JOBINPROGRESS j	
		INNER JOIN CTE_Drivers d ON j.driverId = d.driverId AND j.CollectionDatetime = d.LastAllocStDate) A
		INNER JOIN FieldResource B ON A.DriverID = B.ID) t 
)
SELECT Name,  Allocation, AllocStDate, AllocEndDate,ClientName,ID,NotAllocated, CASE WHEN T.Comments IS NULL THEN 'Unallocated' else 'Allocated' END allocated
FROM CTE1 c
LEFT JOIN Transtable1 T on c.Allocated = T.Comments

Select allOpen in new window

Pawan,
The 'Name column in Transtable1  table is the driver's name.
Its not showing correct result.'

WHERE TT.Name = Z.Name 

Select allOpen in new window

must be

WHERE TT.Name = Z.ClientName 

Select allOpen in new window

Updated.

WITH CTE_Drivers AS
(
	SELECT DriverID, MAX(CollectionDatetime) LastAllocStDate
	FROM JOBINPROGRESS
	GROUP BY DriverID
)
,CTE1
AS
(
	SELECT DISTINCT Name,  Allocation, AllocStDate, AllocEndDate,
		CASE
			WHEN OnJob IS NULL THEN NULL
			ELSE ClientName 
		END ClientName, 
		CASE
			WHEN OnJob IS NULL THEN NULL
			ELSE ID 
		END ID, 
		CASE 
			WHEN OnJob IS NULL THEN 'YES'
			ELSE NULL
		END as NotAllocated,
		OnJob as Allocated
	FROM 
		(SELECT B.NAME, A.Allocation,A.AllocStDate,A.AllocEndDate,A.ClientName,A.ID,
		   CASE 
				 WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate  > = CAST(GETDATE() AS date) THEN 'Allocated'
				 ELSE NULL
		   END as OnJob
		FROM (SELECT j.ID, j.ClientName, j.DriverID, CAST(j.CollectionDateTime  AS date) AllocStDate, j.Allocation, CAST(j.AllocEndDate AS date) AllocEndDate
		FROM JOBINPROGRESS j	
		INNER JOIN CTE_Drivers d ON j.driverId = d.driverId AND j.CollectionDatetime = d.LastAllocStDate) A
		INNER JOIN FieldResource B ON A.DriverID = B.ID) t 
)
SELECT Name,  Allocation, AllocStDate, AllocEndDate,ClientName,ID,NotAllocated, CASE WHEN T.Comments IS NULL THEN 'Unallocated' else 'Allocated' END allocated
FROM CTE1 c
LEFT JOIN Transtable1 T on c.Name = T.Name

Select allOpen in new window

Thanks Pawan, will try and brb.

Hello Pawan,
The  query works but only poblem is that the names are repeated. Example
Name
Xyzname
Xyzname

How can i have distinct names.

Regards and thanks

Have you tried to adapt my solution? Cause that is what I meant by "Without further information about cardinality"..

Thanks will try now

This worked perfectly mate. Thanks a ton.
Thanks Pawan for all the efforts!
Really appreciate!

Hello Ste5an

Just a quick question

How to check on this line

   IIF(EXISTS ( SELECT * FROM Transfer TT WHERE TT.driver = Z.Name AND TT.Comments = 'Allocated' ), 'Allocated', '') AS IsAllocatedByTransfer
		

Select allOpen in new window

to check for the enddate as

 IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS IsAllocatedByTransfer

Select allOpen in new window

How to check both conditions?

Thanks

hmm, there is no date column according to your description for Transtable1.

It looks like the expression from your sub-query t. Which is the same in my post as CTE T.. there the column was named OnJob..

so what do you mean exactly?

Yes you are correct. Thereis no date column in Transfer table. But it should check  the enddate to be marked as allocated.

 IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob

Select allOpen in new window

Any suggestions? Thanks.

Please post your concrete query. Cause according to your snippets you mean the OnJob column..

yours:
mine:

Yes thats correct!

Just need one more condition/Check on this line

  IIF(EXISTS ( SELECT * FROM Transfer TT WHERE TT.driver = Z.Name AND TT.Comments = 'Allocated' ), 'Allocated', '') AS IsAllocatedByTransfer

Select allOpen in new window

Check enddate as this .

 IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob

These columns 'AllocStDate' and '.AllocEndDate'are  not the columns of ''Transfer table.

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     JobProgress 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   JobProgress 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 )
SELECT  Z.Name ,
        Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         Z.Allocated ,		 
         IIF(EXISTS ( SELECT * FROM Transfer TT WHERE TT.driver = Z.Name AND TT.Comments = 'Allocated' ), 'Allocated', '') AS IsAllocatedByTransferTab
		
FROM     Z
ORDER BY Z.Allocated desc,IsAllocatedByTransferTab desc;

Select allOpen in new window

Please find the query above.

Did I already mention that there pretty much allocated columns?

Do you mean this:

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     JOBINPROGRESS 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   JOBINPROGRESS 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 )
SELECT   Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,
         Z.Allocated ,
         IIF(T.Allocated = 'Allocated' AND EXISTS ( SELECT * FROM Transtable1 TT WHERE TT.Name = Z.Name AND TT.Comments = 'Allocated' ), 1, 0) AS IsAllocatedByComments
FROM     Z
ORDER BY Z.Allocated DESC;

Select allOpen in new window

??

Thanks will try and brb

Sorry, Nope this didnt work just need and additional condtion on this bit

   IIF(T.Allocated = 'Allocated' AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Name = Z.Name AND TT.Comments = 'Allocated' ), 1, 0) AS IsAllocatedByComments

Select allOpen in new window

Check enddate as this .

 IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob

These columns 'AllocStDate' and '.AllocEndDate'are  not the columns of ''Transfer table.

Yup, Typo:

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     JOBINPROGRESS 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   JOBINPROGRESS 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 )
SELECT   Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,
         Z.Allocated ,
         IIF(Z.Allocated = 'Allocated' AND EXISTS ( SELECT * FROM Transtable1 TT WHERE TT.Name = Z.Name AND TT.Comments = 'Allocated' ), 1, 0) AS IsAllocatedByComments
FROM     Z
ORDER BY Z.Allocated DESC;

Select allOpen in new window

trying...

ste5an,

Sorry don't understand where are you checking

 IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS IsAllocatedByComments
FROM     Z
ORDER BY Z.Allocated DESC; 

Select allOpen in new window

Need additional condtion on IsAllocatedByComments to check enddate.

Please refer this query :

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     JobProgress 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   JobProgress 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 )
SELECT  Z.Name ,
        Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         Z.Allocated ,		 
         IIF(EXISTS ( SELECT * FROM Transfer TT WHERE TT.driver = Z.Name AND TT.Comments = 'Allocated' ), 'Allocated', '') AS IsAllocatedByTransferTab
		
FROM     Z
ORDER BY Z.Allocated desc,IsAllocatedByTransferTab desc;

Select allOpen in new window

Sorry, Additional condition on line number 45 in the above code.

Thanks

Your condition is already "checked" in line 23. Then this column is renamed in line 34. The test in line 42 refers to that. Thus using Z.Allocated.

ste5an,
Sorry mate, then in that case  it has not worked.
Still when EndDate is markedas '25 -nov-2107'  there are rows in the 'IsAllocatedByTransferTab' still shows 'Allocated'.

Thanks

Please find the attached result.
Thanks
TestAllocation.xlsx

And the value of StartDate?

It is         2017-11-19
Please find the attached excel result sheet

Check your condition again: It is

IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob

Select allOpen in new window

and 25-Nov-2017 is clearly <= GETDATE(). Thus the above condition returns NULL. Thus

IIF(Z.Allocated = 'Allocated' AND EXISTS ( SELECT * FROM Transtable1 TT WHERE TT.Name = Z.Name AND TT.Comments = 'Allocated' ), 1, 0) AS IsAllocatedByComments

Select allOpen in new window

Return 0.

Hi,
If i add that condition. There is not a single row in the 'IsAllocatedByComments' column as allocated.

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Jobinprogress 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   Jobinprogress 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 )
SELECT   Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,
         Z.Allocated ,
         IIF(Z.Allocated = 'Allocated' AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' ), 1, 0) AS IsAllocatedByComments
FROM     Z
ORDER BY Z.Allocated DESC;

Select allOpen in new window

Please post a concise and complete example. This includes table DDL as table variables and sample data INSERT statements as runnable T-SQL script. Otherwise it's only guessing on this side.

Ok mate. Will do this first thing tomorrow.

Cheers

sTE5AN , Will ask a new question as this question is already marked Answered and also this is an additional condition.
Cheers