Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: to show full set of two tables

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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