From: michael@gurfinkel.us   
      
   There is an assignment schedule where several entities are assigned on a daily   
   basis, in ID/DATE simple fashion.   
      
   I need to report all IDS per every date in the work dates calendar, including   
   instances where no assignment exists:   
      
   declare @ASSIGNMENTS	table   
   (	ID	int,   
   	XDATE	date   
   );   
   					   
   declare @WORK_DATES	table   
   (	XDATE	date );   
      
   insert into @ASSIGNMENTS (ID, XDATE) values   
   (	100,	'20150506'),   
   (	100,	'20150507'),   
      
   (	222,	'20150505'),   
   (	222,	'20150506');   
      
   insert into @WORK_DATES (XDATE) values   
   (			'20150505'),   
   (			'20150506'),   
   (			'20150507'),   
   (			'20150508');   
      
   -- My solution is:   
   WITH FULL_SET (XDATE, ID) as   
   (   
   	select DTS.XDATE   
   	,	IDS.ID   
   	from @WORK_DATES DTS   
   	cross apply (   
   		select distinct ID from @ASSIGNMENTS   
   	) IDS   
   )   
   select   
   	F.XDATE   
   ,	A.ID   
   from FULL_SET F   
   left join @ASSIGNMENTS A on F.ID = A.ID and F.XDATE = A.XDATE   
      
      
   I am getting :   
      
   XDATE	ID   
   2015-05-05	NULL   
   2015-05-05	222   
   2015-05-06	100   
   2015-05-06	222   
   2015-05-07	100   
   2015-05-07	NULL   
   2015-05-08	NULL   
   2015-05-08	NULL   
      
   It works OK, but I suspect my solution could (and should) be simplified. Any   
   ideas?   
   Thanks in advance.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)