• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 53
  • Last Modified:

Date format in the query

Hi,
i have a query which works fine.
Only issue here is the AllocStDate displays '01 Jan 1900'
Any suggestions are appreciated.
;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


Regards
0
RIAS
Asked:
RIAS
  • 3
  • 3
1 Solution
 
Mark WillsTopic AdvisorCommented:
As per the other thread use convert(varchar ...) else ''

Code is back there.
0
 
brad2575Commented:
You can use FORMAT to write your own format on the date column:

SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' )
0
 
Mark WillsTopic AdvisorCommented:
case when Allocated='Yes' or IsAllocatedByTransferTab='Allocated' then convert(varchar(20),AllocStDate,120) else '' end as AllocStDate

Open in new window


but dont know what date format you want , nor date datatypes used - see style code https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RIASAuthor Commented:
Mark trying and brb
0
 
RIASAuthor Commented:
Thanks Mark, perfect!!!
0
 
Mark WillsTopic AdvisorCommented:
Yay :)
0
 
RIASAuthor Commented:
;)
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now