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?

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

É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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
ZberteocCommented:
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

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
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
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.