Scenario of getting first and last communication date of each customer

This is a scenario where I need to get the first and last communication date of each customer from a customer table along with corresponding communication Id and SourceCodeId


WITH cte AS(SELECT 
 [CustomerId] 
 ,sc.CompanyId

 ,MIN(CONVERT(VARCHAR, cm.CommunicationDate,121) + CONVERT(VARCHAR, cm.CommunicationId)+ '*' + CONVERT(VARCHAR,cm.SourceCodeId)) AS FirstCommunicationData
 ,MAX(CONVERT(VARCHAR, cm.CommunicationDate,121) + CONVERT(VARCHAR, cm.CommunicationId) + '*' + CONVERT(VARCHAR,cm.SourceCodeId)) AS LastCommunicationData

FROM dbo.Customer cm
INNER JOIN [dbo].[SourceCode] sc on cm.SourceCodeId = sc.SourceCodeId
WHERE CustomerId = 123 --Just used a customer id for test
GROUP BY CustomerId ,sc.CompanyId)

SELECT [CustomerId]
, CompanyId 
, CONVERT(DATE,SUBSTRING(FirstCommunicationData,1,23),121) [FirstCommunicationDate]
, CONVERT(INT,SUBSTRING(FirstCommunicationData,24,CHARINDEX('*',FirstCommunicationData)-24))[FirstCommunicationId]
, CONVERT(INT,SUBSTRING(FirstCommunicationData,CHARINDEX('*',FirstCommunicationData)+1,(LEN(FirstCommunicationData) - CHARINDEX('*',FirstCommunicationData)+1)))[FirstCommunicationSourceCodeID]
, CONVERT(DATE,SUBSTRING(LastCommunicationData,1,23),121) [LastCommunicationDate]
, CONVERT(INT,SUBSTRING(LastCommunicationData,24,CHARINDEX('*',LastCommunicationData)-24))[LastCommunicationId]
, CONVERT(INT,SUBSTRING(LastCommunicationData,CHARINDEX('*',LastCommunicationData)+1,(LEN(LastCommunicationData) - CHARINDEX('*',LastCommunicationData)+1)))[LastCommunicationSourceCodeID]
FROM cte



Using multiple char index:

WITH CTE AS(SELECT 
 [DonorId] 
 ,sc.CompanyId
 , MIN(CONVERT(VARCHAR,t.[TransactionDate],120) + CONVERT(VARCHAR,t.[TransactionId]) + '*' + CONVERT(VARCHAR,t.SourceCodeId)+ '&'+ CONVERT(VARCHAR,ts.TotalDonation)) [FirstDonationData]                            
 , MAX(CONVERT(VARCHAR,t.[TransactionDate],120) + CONVERT(VARCHAR,t.[TransactionId]) + '*' + CONVERT(VARCHAR,t.SourceCodeId)+ '&'+ CONVERT(VARCHAR,ts.TotalDonation)) [LastDonationData]
 , MAX(CONVERT(Binary(10),ts.TotalDonation) + CONVERT(Binary(10),t.[TransactionDate]) + CONVERT(Binary(10),t.[TransactionId]) + CONVERT(Binary(10),t.[SourceCodeId])) as LargestDonationData
 FROM [dbo].[Transaction] t
 INNER JOIN [dbo].[TransactionCompany] sc on t.TransactionId = sc.TransactionId
 INNER JOIN dbo.TransactionSummary ts ON t.TransactionId = ts.TransactionId
WHERE DonorId = 13383180
GROUP BY [DonorId], sc.CompanyId)

 SELECT DonorId
 , SUBSTRING([FirstDonationData],1,10) [FirstDonationDate]
 , REPLACE(SUBSTRING([FirstDonationData],11,LEN([FirstDonationData])), (SUBSTRING([FirstDonationData],CHARINDEX('*',[FirstDonationData]),LEN([FirstDonationData]))),'') [FirstDonationID]
 , REPLACE((SUBSTRING([FirstDonationData],CHARINDEX('*',[FirstDonationData])+1,LEN([FirstDonationData]))),(SUBSTRING([FirstDonationData],CHARINDEX('&',[FirstDonationData]),LEN([FirstDonationData]))),'') [FirstDonationSourceCodeId]
 , SUBSTRING([FirstDonationData],CHARINDEX('&',[FirstDonationData])+1,LEN([FirstDonationData])) [FirstDonationAmount]
 , SUBSTRING([LastDonationData],1,10) [LastDonationDate]
 , REPLACE(SUBSTRING([LastDonationData],11,LEN([LastDonationData])), (SUBSTRING([LastDonationData],CHARINDEX('*',[LastDonationData]),LEN([LastDonationData]))),'') [LastDonationID]
 , REPLACE((SUBSTRING([LastDonationData],CHARINDEX('*',[LastDonationData])+1,LEN([LastDonationData]))),(SUBSTRING([LastDonationData],CHARINDEX('&',[LastDonationData]),LEN([LastDonationData]))),'') [LastDonationSourceCodeId]
 , SUBSTRING([LastDonationData],CHARINDEX('&',[LastDonationData])+1,LEN([LastDonationData])) [LastDonationAmount]
 , CONVERT(money,SUBSTRING(LargestDonationData,1,10)) [LargestDonationAmount]
 , CONVERT(DATE,SUBSTRING(LargestDonationData,11,10)) [LargestDonationDate]
 , CONVERT(INT,SUBSTRING(LargestDonationData, 21,10)) [LargestDonationID]
 , CONVERT(INT,SUBSTRING(LargestDonationData,31,10)) [LargestDonationSourceCodeID]

FROM CTE