SELECT [t].[CreationDate], [t].[BatchId], [t].[Content], [t].[ProspectId], [t1].[CreationDate], [t1].[BatchId], [t1].[MessageContent], [t1].[ProspectId], [t1].[c], [t4].[LeadStatus], [t4].[PushedToCrm], [t4].[ProspectId], [t4].[CampaignId], [t4].[CampaignName], [t4].[DatePushedToCRM], [t4].[c]
FROM [TextMessages] AS [t]
LEFT JOIN (
SELECT [t2].[CreationDate], [t2].[BatchId], [t2].[MessageContent], [t2].[ProspectId], [t2].[c]
FROM (
SELECT [t3].[CreationDate], [t3].[BatchId], [t3].[Content] AS [MessageContent], [t3].[ProspectId], 1 AS [c], ROW_NUMBER() OVER(PARTITION BY [t3].[ProspectId] ORDER BY [t3].[CreationDate]) AS [row]
FROM [TextMessages] AS [t3]
WHERE [t3].[IsIncoming] = CAST(1 AS bit) AND [t3].[TenantId] = @__tenantId_0
) AS [t2]
WHERE [t2].[row] <= 1
) AS [t1] ON [t].[ProspectId] = [t1].[ProspectId]
OUTER APPLY (
SELECT TOP(1) [p].[LeadStatus], [p].[PushedToCRM] AS [PushedToCrm], [t].[ProspectId], [p].[CampaignId], (
SELECT TOP(1) [c].[Title]
FROM [Campaigns] AS [c]
WHERE [c].[Id] = [p].[CampaignId]) AS [CampaignName], CASE
WHEN [p].[PushedToCRM] = CAST(1 AS bit) THEN COALESCE((
SELECT TOP(1) [a].[CreationTime]
FROM [Activities] AS [a]
WHERE [t].[ProspectId] = [a].[ProspectId] AND [a].[Detail] = N'Pushed to CRM'), '0001-01-01T00:00:00.0000000Z')
ELSE NULL
END AS [DatePushedToCRM], 1 AS [c]
FROM [ProspectAdditions] AS [p]
WHERE [p].[ProspectId] = [t].[ProspectId] AND [p].[TenantId] = @__tenantId_0
) AS [t4]
WHERE [t].[TenantId] = @__tenantId_0 AND [t].[CreationDate] >= @__startDate_1 AND [t].[CreationDate] <= @__endDate_2 AND [t].[IsIncoming] = CAST(0 AS bit) AND (
SELECT TOP(1) [t0].[Content]
FROM [TextMessages] AS [t0]
WHERE [t0].[IsIncoming] = CAST(1 AS bit) AND [t0].[TenantId] = @__tenantId_0 AND ([t0].[ProspectId] = [t].[ProspectId] OR ([t0].[ProspectId] IS NULL AND [t].[ProspectId] IS NULL))
...