asked on

Sql query with a condition

Hello,
Need to add a condition in the this existing query

;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

Ryan Chong

try

case when Z.Allocated is null then 'Yes' else Z.Allocated end Allocated

Pawan Kumar

In the last select

,Case when notAllocated is NULL then 'Yes'
Else z.allocated end allocated

RIAS

ASKER

Sorry Pawan,
Can you please repost the query as am getting error

The column 'allocated' was specified multiple times for 'u'.

Pawan Kumar

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.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 ,Case when notAllocated is NULL then  'Yes'
		Else z.allocated end 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

Pawan Kumar

Edited my last comment. Please refresh the page.

RIAS

ASKER

Sorry Pawan,
Its not working its still null .

Thanks

Pawan Kumar

data please in excel ? which column are you referring?

RIAS

ASKER

Pawan,
Please find the excel sheet.
Thanks
ExpectedResult3.xlsx

Pawan Kumar

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 )
,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 ,Case when Z.Allocated is NULL then 'Yes'
		Else z.allocated end 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

RIAS

ASKER

Trying..

RIAS

ASKER

Pawan , now it is 'Yes' on the entire column.
The column IsAllocated should be yes only when 'Not allocated' is 'Null'

Thanks

Pawan Kumar

;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 ,Case when Z.NotAllocated is NULL then 'Yes'
		Else z.allocated end 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

RIAS

ASKER

Trying...

RIAS

ASKER

Sorry Pawan,
Still not correct.
Please find the attached sheet.

Thanks
ExpectedResult3.xlsx

RIAS

ASKER

Just updated the excel sheet with a highlighter.
Please redownload.

Pawan Kumar

got it.

RIAS

ASKER

Sorry ,
Please have a look at allocated column

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

I would do it in that final select ie

intead of

SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate	, u.ClientName ,u.ID,u.NotAllocated,u.Allocated

Open in new window

try

SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate	, u.ClientName ,u.ID,u.NotAllocated, case when u.notallocated is NULL then 'Y' else u.Allocated end Allocated

Open in new window

Pawan Kumar

Mark,

I have done the same in my last comment. :)

CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated

RIAS

ASKER

ThanksPawan!

RIAS

ASKER

Thanks Mark for the efforts.

RIAS

ASKER

Brilliant Pawan!!!

Pawan Kumar

Welcome RIAS.
Glad to help as always :)

RIAS

ASKER

Pawan,
There is one more requirement in this query I am posting a new question.
Please have a look.
Thanks

Pawan Kumar

okies.

Mark Wills

@pawan, you always seem to submit too many posts for me to read each code snippet in detail.... sorry about that.

@RIAS, A pleasure albeit a little late to the party...

FYI there are a couple of SQL statements you could have used. ISNULL or COALESCE or IIF with a couple of examples below for future reference :

declare @field varchar(20)
select ISNULL(@field,'Y')


declare @field1 varchar(20)
declare @field2 varchar(20) = 'yes'
select coalesce(@field1,@field2,'Dunno')

Open in new window