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?

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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
RIASAuthor Commented:
Sorry Pawan,
Can you please repost the query as am getting error

The column 'allocated' was specified multiple times for 'u'.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Pawan KumarDatabase 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

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