SELECT Records Where Date Date On Each Row

Question

I am working in Microsoft SQL Server

I have a table like below for Campaign Responses :

Id       Name           CreateDate
001      Tom Cruise     2018-08-29
002      Tom Hanks      2018-09-02
003      Bill Nye       2018-06-25
004      Johnny Cash    2018-06-27

I also have a table like below for General Activities:

Id   PersonId  Name         ActivityDate
117  001       Tom Cruise   2018-08-30
118  001       Tom Cruise   2018-09-31
211  003       Bill Nye     2018-06-26
212  003       Bill Nye     2018-06-27

I want to create a query that COUNTS the number of activities each campaign member has had AFTER the CreateDate in the Campaign Responses table.

I have tried below, but this only gives me records where there have been Activities after the initial response. I would like to see ALL campaign members and activities after response, even if activities = 0.

SELECT
  a.Id, 
  a.Name,
  a.CreateDate,
  b.XCount as ActivitiesAfterResponse
FROM [Campaign Responses] as a
LEFT JOIN 
(
  SELECT
    PersonId,
    ActivityDate,
    COUNT(DISTINCT(Id)) as XCount
  FROM [General Activities]
  GROUP BY PersonId, ActivityDate
) as b on a.Id = b.PersonId

WHERE b.ActivityDate > a.CreateDate

Ideal results would be:

 Id      Name           CreateDate   ActivitesAfterResponse
001      Tom Cruise     2018-08-29           2
002      Tom Hanks      2018-09-02           0
003      Bill Nye       2018-06-25           2   
004      Johnny Cash    2018-06-27           0

user_1073631 · Answer

You should move your where criteria to the join. It's currently negating your outer join and not returning those players without records. I've also simplified your query a bit -- no need for the subquery from what I can tell:

select r.id, r.name, r.createdate, count(a.id) ActivitesAfterResponse
from [Campaign Responses] r
   left join  [General Activities] a on r.id = a.PersonId 
                                    and a.activitydate > r.createdate 
group by r.id, r.name, r.createdate

user_87015 · Answer

You can use CROSS APPLY instead of grouping on all columns when you don't have to:

SELECT #cr.*, ActivityCount
FROM #cr
CROSS APPLY (
    SELECT COUNT(*) AS ActivityCount
    FROM #ga
    WHERE #ga.PersonID = #cr.Id AND #ga.ActivityDate >= #cr.CreateDate
) AS ca

user_8584235 · Answer

Change your where clause to :

WHERE b.ActivityDate > a.CreateDate or b.ActivityDate is null

SELECT Records Where Date > Date On Each Row

3 Answers