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
RIASAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Got it. 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,
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

Open in new window

1
 
Ryan ChongCommented:
try

case when Z.Allocated is null then 'Yes' else Z.Allocated end Allocated
1
 
Pawan KumarDatabase ExpertCommented:
In the last select

,Case when notAllocated is NULL then  'Yes'
Else z.allocated end allocated
1
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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

The column 'allocated' was specified multiple times for 'u'.
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.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

0
 
Pawan KumarDatabase ExpertCommented:
Edited my last comment. Please refresh the page.
0
 
RIASAuthor Commented:
Sorry Pawan,
Its not working its still null .

Thanks
0
 
Pawan KumarDatabase ExpertCommented:
data please in excel ? which column are you referring?
0
 
RIASAuthor Commented:
Pawan,
Please find the excel sheet.
Thanks
ExpectedResult3.xlsx
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 )
,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

1
 
RIASAuthor Commented:
Trying..
0
 
RIASAuthor Commented:
Pawan , now it is 'Yes' on the entire column.  
The column IsAllocated should be yes only when 'Not allocated' is 'Null'

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

1
 
RIASAuthor Commented:
Trying...
0
 
RIASAuthor Commented:
Sorry Pawan,
Still not correct.
Please find the attached sheet.

Thanks
ExpectedResult3.xlsx
0
 
RIASAuthor Commented:
Just updated the excel sheet with a highlighter.
Please redownload.
0
 
Pawan KumarDatabase ExpertCommented:
got it.
0
 
RIASAuthor Commented:
Sorry ,
Please have a look at allocated column
0
 
Mark WillsTopic AdvisorCommented:
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

1
 
Pawan KumarDatabase ExpertCommented:
Mark,

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

CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
0
 
RIASAuthor Commented:
ThanksPawan!
1
 
RIASAuthor Commented:
Thanks Mark for the efforts.
0
 
RIASAuthor Commented:
Brilliant Pawan!!!
0
 
Pawan KumarDatabase ExpertCommented:
Welcome RIAS.
Glad to help as always :)
0
 
RIASAuthor Commented:
Pawan,
There is one more requirement in this query I am posting a new question.
Please have a look.
Thanks
0
 
Pawan KumarDatabase ExpertCommented:
okies.
0
 
Mark WillsTopic AdvisorCommented:
@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

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