Sql query with join

Hello,
Need a suggestion on a join query this is in continuation of the question below
https://www.experts-exchange.com//questions/29026560/SQL-Query.html
WITH CTE_Drivers AS
(
	SELECT DriverID, MAX(CollectionDatetime) LastAllocStDate
	FROM JOBINPROGRESS
	GROUP BY DriverID
)
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

Open in new window


How can I add one more table Transtable1 in the above query to validate on allocated.

Table: Transtable1
Column: Comments

If Comments = Allocated then mark the  ‘Allocation’ is ‘Allocated’


Regards
RIASAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Without further Information about cardinality:

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

Open in new window


But: now we have three different kinds of "allocated" calculations..
0
 
ste5anSenior DeveloperCommented:
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;

Open in new window


And check whether this is correct:

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

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

Open in new window

is a better solution.

And thinking positive is the best:

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

Open in new window


How is your Transtable1 related to the others?
0
 
Pawan KumarDatabase ExpertCommented:
How this table (Transtable1) is joined with others?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
Hello,
This is not yet joined but need to join it in the query
0
 
Pawan KumarDatabase ExpertCommented:
which column from the Transtable1 is joined with other table's column
0
 
RIASAuthor Commented:
Common columns are Name

Table: Transtable1
Column: Comments
Column : Name
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

1
 
RIASAuthor Commented:
Will try
0
 
RIASAuthor Commented:
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'.
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

0
 
RIASAuthor Commented:
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'allocated'.
0
 
RIASAuthor Commented:
SELECT Name,  Allocation, AllocStDate, AllocEndDate,ClientName,ID,NotAllocated, CASE WHEN T.Comments IS NULL THEN 'Unallocated' else 'Allocated' allocated
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

0
 
RIASAuthor Commented:
Pawan,
The 'Name column in Transtable1  table is the driver's name.
Its not showing correct result.'
0
 
ste5anSenior DeveloperCommented:
WHERE TT.Name = Z.Name 

Open in new window

must be
WHERE TT.Name = Z.ClientName 

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

1
 
RIASAuthor Commented:
Thanks Pawan, will try and brb.
0
 
RIASAuthor Commented:
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
0
 
ste5anSenior DeveloperCommented:
Have you tried to adapt my solution? Cause that is what I meant by "Without further information about cardinality"..
1
 
RIASAuthor Commented:
Thanks will try now
0
 
RIASAuthor Commented:
This worked perfectly mate. Thanks a ton.
Thanks Pawan for all the efforts!
Really appreciate!
0
 
RIASAuthor Commented:
Hello Ste5an
0
 
RIASAuthor Commented:
Just a quick question
0
 
RIASAuthor Commented:
How to check on this line
   IIF(EXISTS ( SELECT * FROM Transfer TT WHERE TT.driver = Z.Name AND TT.Comments = 'Allocated' ), 'Allocated', '') AS IsAllocatedByTransfer
		

Open 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

Open in new window

0
 
RIASAuthor Commented:
How to check both conditions?

Thanks
0
 
ste5anSenior DeveloperCommented:
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?
0
 
RIASAuthor Commented:
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

Open in new window

0
 
RIASAuthor Commented:
Any suggestions? Thanks.
0
 
ste5anSenior DeveloperCommented:
Please post your concrete query. Cause according to your snippets you mean the OnJob column..

yours:
Capture.PNGmine:
Capture2.PNG
0
 
RIASAuthor Commented:
Yes thats correct!
0
 
RIASAuthor Commented:
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

Open 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.
0
 
RIASAuthor Commented:
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;

Open in new window

0
 
RIASAuthor Commented:
Please find the query above.
0
 
ste5anSenior DeveloperCommented:
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;

Open in new window


??
0
 
RIASAuthor Commented:
Thanks will try and brb
0
 
RIASAuthor Commented:
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

Open in new window

0
 
RIASAuthor Commented:
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.
0
 
ste5anSenior DeveloperCommented:
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;

Open in new window

1
 
RIASAuthor Commented:
trying...
0
 
RIASAuthor Commented:
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; 

Open in new window

0
 
RIASAuthor Commented:
Need additional condtion on IsAllocatedByComments to check enddate.
0
 
RIASAuthor Commented:
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;

Open in new window

0
 
RIASAuthor Commented:
Sorry, Additional condition on line number 45 in the above code.

Thanks
0
 
ste5anSenior DeveloperCommented:
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.
0
 
RIASAuthor Commented:
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
0
 
RIASAuthor Commented:
Please find the attached result.
Thanks
TestAllocation.xlsx
0
 
ste5anSenior DeveloperCommented:
And the value of StartDate?
0
 
RIASAuthor Commented:
It is         2017-11-19
Please find the attached excel result sheet
0
 
ste5anSenior DeveloperCommented:
Check your condition again: It is

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

Open 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

Open in new window

Return 0.
1
 
RIASAuthor Commented:
Hi,
If i add that condition. There is not a single row in the 'IsAllocatedByComments' column as allocated.
0
 
RIASAuthor Commented:
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;

Open in new window

0
 
ste5anSenior DeveloperCommented:
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.
0
 
RIASAuthor Commented:
Ok mate. Will do this first thing tomorrow.

Cheers
0
 
RIASAuthor Commented:
sTE5AN , Will ask a new question as this question is already marked Answered and also this is an additional condition.
Cheers
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.