Select missing dates within a date range

Team
TeamId TeamName
1 SAS
2 MIN
3 CHI
4 TOR
5 MIA
Schedule
ScheduleId HomeTeamId VisitorTeamId GameDate
1 1 2 2017-10-18 20:30:00.0000000
2 3 1 2017-10-21 19:00:00.0000000
3 1 4 2017-10-23 19:30:00.0000000
4 5 1 2017-10-25 19:00:00.0000000 
	
	DECLARE @StartDate DATE, @EndDate DATE;

	SET @StartDate = '2017-10-18';
	SET @EndDate = '2017-10-25';

	WITH DateRange(Date)
		 AS (
			 SELECT @StartDate Date
			 UNION ALL
			 SELECT DATEADD(day, 1, Date) Date
			 FROM DateRange
			 WHERE Date < @EndDate
			)

		 SELECT 'SAS' AS Team,
				Date
		 FROM DateRange
		 EXCEPT
		 SELECT TeamName,
				CAST(GameDate AS DATE)
		 FROM Schedule 
		 INNER JOIN Team ON Schedule.HomeTeamId = Team.TeamId OR
							Schedule.VisitorTeamId = Team.TeamId
		 WHERE TeamName = 'SAS'

		 OPTION(MAXRECURSION 10000);

Result

Between 18.10.2017 and 25.10.2017 SAS doesn't play on the following days:

Team Date
SAS 2017-10-19
SAS 2017-10-20
SAS 2017-10-22
SAS 2017-10-24
1
1

Related Articles