Query not to display StartDate depending on the condition

Hello,
I have a query which works fine. Need a small change in it.
;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, u.Comments,u.Principal
	FROM 
	(
		SELECT Z.Name,	Z.Allocation ,Z.allocated 	,  TT.Comments, TT.Principal,
		CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated'  or  TT.Comments = 'AllocationChanged'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'  or  TT.Comments = 'AllocationChanged' 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'  or  TT.Comments = 'AllocationChanged' 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	,  CASE WHEN Comments = 'Allocated' THEN Principal ELSE ClientName END  ClientName	
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
 end IsAllocatedByTransferTab
FROM CTE2
order by notallocated asc

Open in new window


The query displays a  AllocStDate. The change required is to display AllocStDate only is Allocated is 'Yes'  or IsAllocatedByTransferTab is 'Allocated'  otherwise it should be blank .
Thanks
RIASAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
And an extra step and then simply use a CASE statement:
;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, u.Comments,u.Principal
	FROM 
	(
		SELECT Z.Name,	Z.Allocation ,Z.allocated 	,  TT.Comments, TT.Principal,
		CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated'  or  TT.Comments = 'AllocationChanged'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'  or  TT.Comments = 'AllocationChanged' 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'  or  TT.Comments = 'AllocationChanged' 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 
)
,final_cte as(
	SELECT Name	,Allocation,	AllocStDate,	AllocEndDate	,  CASE WHEN Comments = 'Allocated' THEN Principal ELSE ClientName END  ClientName	
	,ID
	,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
	,IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
	,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
	 end IsAllocatedByTransferTab
	FROM CTE2
)
select
	 Name
	,Allocation
	,case when Allocated='Yes' or IsAllocatedByTransferTab='Allocated' then AllocStDate else '' end as AllocStDate
	,AllocEndDate
	,ClientName
	,ID
	,NotAllocated
	,Allocated
	,IsAllocatedByTransferTab
from
	final_cte
order by 
	notallocated asc

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
maybe you can try this:

SELECT Name	,Allocation
,  case when IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) and IsAllocatedByTransferTab = 'Allocted' then AllocStDate
        else null
end as AllocStDate
,	AllocEndDate	,  CASE WHEN Comments = 'Allocated' THEN Principal ELSE ClientName END  ClientName	
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
 end IsAllocatedByTransferTab
FROM CTE2
order by notallocated asc

Open in new window

0
 
RIASAuthor Commented:
Thanks Eric, will try and brb
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark WillsTopic AdvisorCommented:
Taking it from the final SELECT we find :

,IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab )
 end IsAllocatedByTransferTab

They both share :  AllocStDate > AllocEndDate AND Comments='Allocated'  to give 'YES' and 'ALLOCATED' respectively, so that should be the criteria for testing display of ALLOCSTDATE

So, please try
SELECT Name	,Allocation
, CASE WHEN AllocStDate > AllocEndDate AND Comments='Allocated' then convert(varchar(20), AllocStDate, 120) else '' end as AllocStDate
, AllocEndDate	,  CASE WHEN Comments = 'Allocated' THEN Principal ELSE ClientName END  ClientName	
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) 
 end IsAllocatedByTransferTab
FROM CTE2
order by notallocated asc

Open in new window

I use the CONVERT function to get the date in a display format - you might want to change depending on style code and length.
Currently, convert(varchar(20),getdate(),120) will show as : 2018-03-26 00:13:59  (my time)

And IIF SyntaxPreference = 'New'  (pardon the pun) then

, IIF( AllocStDate > AllocEndDate AND Comments='Allocated' , convert(varchar(20), AllocStDate, 120) , '') as AllocStDate
0
 
RIASAuthor Commented:
Thanks Mark! will try and brb!
0
 
Mark WillsTopic AdvisorCommented:
You want to check to display AllocStDate only if Allocated is 'Yes'  or IsAllocatedByTransferTab is 'Allocated'  otherwise it should be blank

We see in your CTE1 query from Z that IsAllocatedByTransferTab can be derived from :  
IIF( 1=1  AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' ) , 'Allocated', '') AS IsAllocatedByTransferTab

Which means we probably want to check :
, IIF( (AllocStDate > AllocEndDate AND Comments='Allocated') or (IsAllocatedByTransferTab = 'Allocated') , convert(varchar(20), AllocStDate, 120) , '') as AllocStDate and u.NotAllocated IS NULL

But I would probably stick with my first suggestion being the common denominator for the latest rendition of 'Yes' or 'Allocated' but there is the outside chance that IsAllocatedByTransferTab = 'Allocated' was generated from CTE1
0
 
Scott PletcherSenior DBACommented:
I suggest using a CROSS APPLY to go ahead and assign the final column name to the two calc'd "Allocated" columns, then test those columns in the main query with a CASE statement.  Finally, in order to display a blank, will have to convert the date to varchar: naturally change the format code if you need a different display format.

...all_ctes_same_as_before...
SELECT Name      ,Allocation
,CASE WHEN alias1.Allocated = 'Allocated' OR alias1.IsAllocatedByTransferTab = 'Allocated'
      THEN CONVERT(varchar(10), AllocStDate, 101) ELSE '' END AS AllocStDate

,AllocEndDate,  CASE WHEN Comments = 'Allocated' THEN Principal ELSE ClientName END  ClientName      
,ID
,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
,alias1.Allocated
,alias1.iIsAllocatedByTransferTab
FROM CTE2
CROSS APPLY (
    SELECT
    IIF ( AllocStDate > AllocEndDate AND Comments='Allocated' , 'Yes'   , Allocated  ) Allocated
    ,case when AllocStDate > AllocEndDate AND Comments='Allocated' then 'Allocated' else   IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab )
     end IsAllocatedByTransferTab
) AS alias1

order by notallocated asc
0
 
RIASAuthor Commented:
Plz ignore my previous comment. Thanks
0
 
RIASAuthor Commented:
Zberteoc,
Thanks you query worked but I am getting date '01 Jan 1900' instead of blank.
0
 
RIASAuthor Commented:
Thanks!
0
 
Mark WillsTopic AdvisorCommented:
*laughing* I give up !!!!

Did you even try my post ?
0
 
RIASAuthor Commented:
Mark,
Yes, Mark i did ... it worked fine but it didnt display StartDate even when allocation was set to 'Yes'
0
 
RIASAuthor Commented:
on two rows.
0
 
RIASAuthor Commented:
Thanks mate for coming back
0
 
Mark WillsTopic AdvisorCommented:
Might have been nice to know that 3d ago ;)

probably u.NotAllocated IS NULL

could have solved that, and reckon the date probably displayed as something or blank....
0
 
ZberteocCommented:
Thanks you query worked but I am getting date '01 Jan 1900' instead of blank.
That is because I the CASE statement has to show either AllocStDate or '' but the types are different, I suppose, so by default it converts '' to a date, which is Jan 1 1900. A CONVERT in that line will solve the problem:

      ,case when Allocated='Yes' or IsAllocatedByTransferTab='Allocated' then CONVERT(varchar(30), AllocStDate, 121) else '' end as AllocStDate
1
 
RIASAuthor Commented:
Thanks!
0
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.