sql query

Hello,

I have this query which works perfectly but need few modifications :

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 )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         Z.Allocated ,		
         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 IIF( 1=1  
		 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 , 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

END

Open in new window


Need to have few changes :
In table Transfer

SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' 

Open in new window

need to have additional condition  :   TT.Comments = 'Transfer'
and then start date  '    Z.AllocStDate ' should be replaced by TT.TrDate


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

Pawan KumarDatabase ExpertCommented:
Please try this -

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 )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         TT.TrDate ,		
         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 IIF( 1=1  
		 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 , 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

1
RIASAuthor Commented:
Trying...
0
RIASAuthor Commented:
Thanks Pawan,
Only thing here is that

start date  '    Z.AllocStDate ' should be replaced by TT.TrDate when ( TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' )
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

RIASAuthor Commented:
Also Pawan ,
if TT.Comments = 'Transfer' then column 'IsAllocatedByTransferTab' should be 'Transfer'
if TT.Comments = 'Allocated' then column 'IsAllocatedByTransferTab' should be 'Allocated'


Thanks
0
Pawan KumarDatabase ExpertCommented:
Updated. Please try this -

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 )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END [start date],
		 CASE WHEN 	TT.Comments = 'Transfer' THEN 'Transfer' 
			  WHEN TT.Comments = 'Allocated' THEN  'Allocated'
				END AS [IsAllocatedByTransferTab]
         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 IIF( 1=1  
		 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 , 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Sorry last one has syntax problem. pls use this-

;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 )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END [start date],
		 CASE WHEN 	TT.Comments = 'Transfer' THEN 'Transfer' 
			  WHEN TT.Comments = 'Allocated' THEN  'Allocated'
				END AS [IsAllocatedByTransferTab],
         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 IIF( 1=1  
		 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 , 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

1
RIASAuthor Commented:
Got an error:

Ambiguous column name 'IsAllocatedByTransferTab'.
0
Pawan KumarDatabase ExpertCommented:
Please try this -

yes we have 2 columns..so..

;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 )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END [start date],
		 CASE WHEN 	TT.Comments = 'Transfer' THEN 'Transfer' 
			  WHEN TT.Comments = 'Allocated' THEN  'Allocated'
				END AS [IsAllocatedByTransferTabNew],
         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 IIF( 1=1  
		 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 , 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

1
RIASAuthor Commented:
Pawan,
Thanks but the column 'IsAllocatedByTransferTab' still showing is 'Allocated'  and cannot see 'Transfer'

Thanks
0
Pawan KumarDatabase ExpertCommented:
So what we need in this column - IsAllocatedByTransferTab?

That is the old column i guess..

CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE
             IIF( 1=1  
             AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
             , 'Allocated', '') END AS IsAllocatedByTransferTab

our new column is this IsAllocatedByTransferTabNew
0
RIASAuthor Commented:
IsAllocatedByTransferTab should have 'Transfer' or 'Allocated ' depending on the TT.comments

example :
  TT.comments ='Transfer' then IsAllocatedByTransferTab  ='Transfer'
  TT.comments ='Allocated' then IsAllocatedByTransferTab  ='Allocated'


Thanks
0
Pawan KumarDatabase ExpertCommented:
Is this -

;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 )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END [start date],
		 CASE WHEN 	TT.Comments = 'Transfer' THEN 'Transfer' 
			  WHEN TT.Comments = 'Allocated' THEN  'Allocated'
				END AS IsAllocatedByTransferTab
   --      CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 --IIF( 1=1  
		 --AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 --, 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

0
RIASAuthor Commented:
Sorry Pawan,
Now IsAllocatedByTransferTab is all Transfer.

Thanks
0
RIASAuthor Commented:
Also  sorry
start date '    Z.AllocStDate ' should be replaced by TT.TrDate when ( TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' )

Thanks
0
Pawan KumarDatabase ExpertCommented:
it is already there , check this column [start date],

CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END [start date],
0
RIASAuthor Commented:
Hello Pawan,
Thanks but I meant we don't need the column StartDate , the start date should be dispayed in the column AllocStDate
0
RIASAuthor Commented:
Also the names are not distinct.

Thanks Pawan
0
Pawan KumarDatabase ExpertCommented:
Updated..

;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 )
SELECT   DISTINCT 
		Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate,
		 CASE WHEN 	TT.Comments = 'Transfer' THEN 'Transfer' 
			  WHEN TT.Comments = 'Allocated' THEN  'Allocated'
				END AS IsAllocatedByTransferTab
   --      CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 --IIF( 1=1  
		 --AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 --, 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

1
RIASAuthor Commented:
Hats off Pawan ! trying
0
RIASAuthor Commented:
Got an Error :

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Also  when i removed distinct the  IsAllocatedByTransferTab is all 'Transfer' there is no allocated displayed.

Thanks
0
Pawan KumarDatabase ExpertCommented:
Removed distinct..

;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 )
SELECT   
		Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
         Z.NotAllocated ,		
         CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate,
		 CASE WHEN 	TT.Comments = 'Transfer' THEN 'Transfer' 
			  WHEN TT.Comments = 'Allocated' THEN  'Allocated'
				END AS IsAllocatedByTransferTab
   --      CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
		 --IIF( 1=1  
		 --AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
		 --, 'Allocated', '') END AS IsAllocatedByTransferTab
FROM     Z
	     LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name AND TT.Comments = 'Transfer' 
ORDER BY Z.Allocated DESC, IsAllocatedByTransferTab desc;

Open in new window

0
RIASAuthor Commented:
Nope, the names are repeated and  IsAllocatedByTransferTab is all 'Transfer' there is no allocated displayed.
0
Pawan KumarDatabase ExpertCommented:
can you present the final data and the expected from that. Thanks
0
RIASAuthor Commented:
ok.Thanks
0
RIASAuthor Commented:
Pawan,
I am going away from my PC but will do first tom morning.
As always thanks .
0
RIASAuthor Commented:
We are nearly there but few tweaks are needed .
1
Pawan KumarDatabase ExpertCommented:
Sure no problem :)
1
RIASAuthor Commented:
Thanks.
0
RIASAuthor Commented:
Hello Pawan,
Please find the attached copy of the query expected result.
Thanks
TransferExpectedResult.xlsx
0
RIASAuthor Commented:
Have made some changes in the excel sheet. Please have a look at the new copy.
Thanks
0
Pawan KumarDatabase ExpertCommented:
Please attach new copy of excel.
0
RIASAuthor Commented:
If you redownload  the same one, its the new one.
0
RIASAuthor Commented:
Is it fine Pawan?
0
Pawan KumarDatabase ExpertCommented:
Yes i got this.. changing..
0
RIASAuthor Commented:
Thanks!
0
Pawan KumarDatabase ExpertCommented:
Please use this -

;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.AllocStDate ,
			 Z.AllocEndDate ,
			 Z.ClientName ,
			 Z.ID ,
			 Z.NotAllocated ,					 
	         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
			 IIF( 1=1  
			 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
			 , 'Allocated', '') END AS IsAllocatedByTransferTab
	FROM     Z
			 
)
SELECT * FROM 
(
	SELECT Z.Name,	Z.Allocation	, 
	CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
	 ,	Z.AllocEndDate	, Z.ClientName ,	Z.ID , Z.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) rnk 
	FROM CTE1 Z
	LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name
)u WHERE u.rnk = 1

Open in new window

1
RIASAuthor Commented:
Trying..Thanks
0
RIASAuthor Commented:
Thanks a lot Pawan but few tweaks:

1. AllocStDate is not picking the recent date
example:

RepeatedName123            11/12/2017      NULL      NULL      NULL      YES      09/10/2017      Transfer
RepeatedName123            01/11/2017      NULL      NULL      NULL      YES      01/12/2017      Transfer

The result is showing   RepeatedName123            01/11/2017      NULL      NULL      NULL      YES      01/12/2017      Transfer

2.

NotAllocated column is still     showing 'Yes'  whereas it should be 'Null' if it is allocated by Transfer table 'IsAllocatedByTransferTab' column.

3.
Some Drivers are not shown as allocated in the  'IsAllocatedByTransferTab' column though they are 'Allocated'

Thanks
0
Pawan KumarDatabase ExpertCommented:
Updated for case 1
could you please explain case 2. NotAllocated column is still     showing 'Yes'  whereas it should be 'Null' if it is allocated by Transfer table 'IsAllocatedByTransferTab' column.

;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.AllocStDate ,
			 Z.AllocEndDate ,
			 Z.ClientName ,
			 Z.ID ,
			 Z.NotAllocated ,					 
	         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
			 IIF( 1=1  
			 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
			 , 'Allocated', '') END AS IsAllocatedByTransferTab
	FROM     Z
			 
)
SELECT * FROM 
(
	SELECT Z.Name,	Z.Allocation	, 
	CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
	 ,	Z.AllocEndDate	, Z.ClientName ,	Z.ID , Z.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

Open in new window

0
RIASAuthor Commented:
Please find attachment of the case .

Thanks
ExpectedResultCase2.xlsx
0
Pawan KumarDatabase ExpertCommented:
Updated for case 2.

;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.AllocStDate ,
			 Z.AllocEndDate ,
			 Z.ClientName ,
			 Z.ID ,
			 Z.NotAllocated ,					 
	         CASE WHEN AllocEndDate < CAST(GETDATE() AS DATE) THEN '' ELSE 
			 IIF( 1=1  
			 AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
			 , 'Allocated', '') END AS IsAllocatedByTransferTab
	FROM     Z
			 
)
SELECT * FROM 
(
	SELECT Z.Name,	Z.Allocation	, 
	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

Open in new window

1
RIASAuthor Commented:
Hey Pawan, don't know how to thank you mate....will try and brb
1
RIASAuthor Commented:
Pawan,
One small one now:

Future dates also need to be included.
Example:
In Transfer table if the AllocStDate is  15/11/2017 it does not show as allocated .
Also if we sort by : NotAllocated and IsAllocatedByTransferTab
And finally
What is 'rnk' column can we remove it or not display it.


Thanks
0
Pawan KumarDatabase ExpertCommented:
Updated for last comment.

;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.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
			 
)
SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate	, u.ClientName ,u.ID,u.NotAllocated
,u.IsAllocatedByTransferTab
FROM 
(
	SELECT Z.Name,	Z.Allocation	, 
	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

Open in new window

1

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
RIASAuthor Commented:
Thanks trying..
0
RIASAuthor Commented:
Pawan,
It works great!!! Thanks a ton mate ...can't thank you enough !!!

Thanks
0
RIASAuthor Commented:
Really appreciate your help and patience , it was a very complicated query.... I am learning a lot from you.
1
Pawan KumarDatabase ExpertCommented:
Glad to help as always. :)
0
RIASAuthor Commented:
Pawan,
Sorry we missed one column ; have a look at a little amended query 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
			 
)
SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate	, u.ClientName ,u.ID,u.NotAllocated,u.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

Open in new window


I  have added the column   Z.Allocated  can we just add one more condition :
If 'NotAllocated' column is NULL then  Z.Allocated  is 'Yes'

Thanks
0
RIASAuthor Commented:
Pawan I am raising a new question for this.
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.