Groups > comp.databases.ms-sqlserver > #1968

to show full set of two tables

X-Received	by 10.98.26.8 with SMTP id a8mr18240717pfa.4.1469497647417; Mon, 25 Jul 2016 18:47:27 -0700 (PDT)
X-Received	by 10.157.32.22 with SMTP id n22mr989680ota.2.1469497647366; Mon, 25 Jul 2016 18:47:27 -0700 (PDT)
Path	csiph.com!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!news.glorb.com!f6no4661831ith.0!news-out.google.com!d68ni6642ith.0!nntp.google.com!f6no4661824ith.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Mon, 25 Jul 2016 18:47:26 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=71.95.163.250; posting-account=99cyNgoAAAA03l-zLDrnoY7TEbs-AvM9
NNTP-Posting-Host	71.95.163.250
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<f4792cbb-d224-4cc0-ab82-b81d0cbcd87d@googlegroups.com> (permalink)
Subject	to show full set of two tables
From	"M.G." <michael@gurfinkel.us>
Injection-Date	Tue, 26 Jul 2016 01:47:27 +0000
Content-Type	text/plain; charset=UTF-8
Xref	csiph.com comp.databases.ms-sqlserver:1968

Show key headers only | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

to show full set of two tables "M.G." <michael@gurfinkel.us> - 2016-07-25 18:47 -0700
  Re: to show full set of two tables Erland Sommarskog <esquel@sommarskog.se> - 2016-07-26 11:51 +0200

csiph-web