f



to show full set of two tables

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.
0
M
7/26/2016 1:47:26 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
478 Views

Similar Articles

[PageSpeed] 51

M.G. (michael@gurfinkel.us) writes:
> 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: 
>... 
> 
> It works OK, but I suspect my solution could (and should) be simplified.
> Any ideas? 

So two things strikes me as odd here. 

1) If the result set is supposed to show whether there is an assignment for
a certain ID on a certain date, I would expect three columns: date, id and a 
yes/no column. Now there are multiple rows with NULL on the same date if 
there is no assignment at all.

2) I would expect there to be a table to hold the IDs as such. In that case 
the SELECT DISTINCT in the CTE could be replace with a straight SELECT 
from that table.

Else, this is the typical pattern for this type of query.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se
0
Erland
7/26/2016 9:51:23 AM
Reply: