Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL query with a condition

Hello, I have perfectly working query
Need a condition in there. Any suggestions are welcome 
;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
	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 
)
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
FROM CTE2
order by notallocated asc

Open in new window


Please find the excel sheet for the query.

Thanks
ExpectedResult5.xlsx
Avatar of Ganesh Gurudu
Ganesh Gurudu
try this code. modified something

		
		
		;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, 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 
		FROM CTE1 Z
		LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
	)u WHERE u.rnk = 1 
)
SELECT Name	,Allocation,	AllocStDate,	AllocEndDate	,ClientName	
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,IIF ( AllocStDate > AllocEndDate AND TT.Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND TT.Comments='Allocated' then 'Allocated' else  
 IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
 end IsAllocatedByTransferTab
FROM CTE2
order by notallocated asc

Open in new window

Avatar of Vaibhav Goel
Vaibhav Goel
Flag of India image
Hello Rias

;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
      ,TT.TrDate,TT.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
            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
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Will try and brb thanks
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hello Vaibhav,
Got an error
Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.TrDate" could not be bound.
Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.Comments" could not be bound.
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hello Ganesh,
Got an error:
Msg 207, Level 16, State 1, Line 59
Invalid column name 'Comments'.
Avatar of Vaibhav Goel
Vaibhav Goel
Flag of India image
Hello Rias

Please make an attempt to below changed code

;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
      ,TT.TrDate,TT.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

Vaibhav
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks...trying
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Got an error:

Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.TrDate" could not be bound.
Msg 4104, Level 16, State 1, Line 58
The multi-part identifier "TT.Comments" could not be bound.
ASKER CERTIFIED SOLUTION
Avatar of Vaibhav Goel
Vaibhav Goel
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Ganesh Gurudu
Ganesh Gurudu
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks!
Avatar of Vaibhav Goel
Vaibhav Goel
Flag of India image
Please select the answer as solution if you problem is solved
Avatar of RIAS
RIAS
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks