tsql - Getting those records that have a match of all records in another table -

-

within realm of problem have 3 entities:

  1. user
  2. position
  3. license

then have 2 relational (many-to-many) tables:

  1. positionlicense - 1 connects position license ie. licenses required particular position
  2. userlicense - 1 connects user license ie. licenses particular user has. additional complexity: user licenses have validity date range (validfrom , validto)

the problem

these input variables:

  • userid identifiers particular user
  • rangefrom defines lower date range limit
  • rangeto defines upper date range limit

what need get? particular user (and date range) need list of positions particular user can work at. problem user must have @ least licenses required every matching position.

i'm having huge problems writing sql query list.

if @ possible using single sql query (can have additional ctes of course). if can convince me doing in several queries more efficient i'm willing listen in.

some workable data

copy , runs script. 3 users, 3 positions, 6 licenses. mark , john should have match not jane.

create table [user] (     userid int identity not null         primary key,     name nvarchar(100) not null ) go  create table position (     positionid int identity not null         primary key,     name nvarchar(100) not null ) go  create table license (     licenseid int identity not null         primary key,     name nvarchar(100) not null ) go  create table userlicense (     userid int not null         references [user](userid),     licenseid int not null         references license(licenseid),     validfrom date not null,     validto date not null,     check (validfrom < validto),     primary key (userid, licenseid) ) go  create table positionlicense (     positionid int not null         references position(positionid),     licenseid int not null         references license(licenseid),     primary key (positionid, licenseid) ) go  insert [user] (name) values ('mark mechanic'); insert [user] (name) values ('john pilot'); insert [user] (name) values ('jane has arts phd not medical.');  insert position (name) values ('mechanic'); insert position (name) values ('pilot'); insert position (name) values ('doctor');  insert license (name) values ('mecha'); insert license (name) values ('flying'); insert license (name) values ('medicine'); insert license (name) values ('phd'); insert license (name) values ('phycho'); insert license (name) values ('arts');  insert positionlicense (positionid, licenseid) values (1, 1); insert positionlicense (positionid, licenseid) values (2, 2); insert positionlicense (positionid, licenseid) values (2, 5); insert positionlicense (positionid, licenseid) values (3, 3); insert positionlicense (positionid, licenseid) values (3, 4);  insert userlicense (userid, licenseid, validfrom, validto) values (1, 1, '20110101', '20120101'); insert userlicense (userid, licenseid, validfrom, validto) values (2, 2, '20110101', '20120101'); insert userlicense (userid, licenseid, validfrom, validto) values (2, 5, '20110101', '20120101'); insert userlicense (userid, licenseid, validfrom, validto) values (3, 4, '20110101', '20120101'); insert userlicense (userid, licenseid, validfrom, validto) values (3, 6, '20110101', '20120101');

resulting solution

i've setup resulting solution based on accepted answer provides simplified solution problem. if you'd play query hit edit/clone (whether you're logged in or not). can changed:

  • three variables:
    1. two variable set date range (@from , @to)
    2. user id (@user)
  • you can toggle commented code in first cte switch code between overlapping user licenses or partially overlapping ones.

this makes number of assumptions (ignores presence of time in datetime columns, assumes obvious primary keys) , skips joins pull in user name, position details, , like. (and implied user had hold licenses full period specified, right?)

select pl.positionid  positionlicense pl   left outer join (--  licenses user has entirety (sp?) of specified date range                    select licenseid                     userlicense                     userid = @userid                      , @rangefrom <= validfrom                      , @rangeto >= validto) li    on li.licenseid = pl.licenseid  group pl.positionid   --  licenses required position held user  having count(pl.licenseid) = count(li.licenseid)

no data can't debug or test it, or close should trick.


Comments

Post a Comment

Popular posts from this blog

php - What is the difference between $_SERVER['PATH_INFO'] and $_SERVER['ORIG_PATH_INFO']? -

-
what’s difference between $_server['path_info'] , $_server['orig_path_info'] ? how use them? when run print_r($_server) , path_info , orig_path_info not present in array. why not? how can enable them? i have read php manual on them, still don’t understand them. the path_info variable present if invoke php script this: http://www.example.com/phpinfo.php/hello_there it's /hello_there part after .php script. if don't invoke url that, there won't $_server["path_info"] environment variable. the porig_ prefix uncommon. path_info standard cgi-environment variable, , should never prefixed. did read that? (there issues around php3/php4 if invoked php interpreter via cgi-bin/ - hardly has such setups today.) for reference: http://www.ietf.org/rfc/rfc3875
Read more

fortran - Function return type mismatch -

-
i'm attempting recode old c++ program in fortran make use of lapack (i'm aware c++ have lapack++, i'm having lot of trouble installing it, gave up). i didn't have problems compilation, when had variables declared real . when started coding section of program required lapack, found parameters passed dsyev need double precision . tried change double precision (including changing hard coded numbers double precision counterparts, i.e. 0.0 -> 0.0d0) when try compile, following error of functions , subroutines i've written: error: return type mismatch of function <function> @ (1) (real(4)/real(8)) i'm not sure coming from, in program has been changed double precision. for example, i've declared following: double precision :: alpha(3),d(3),zeta1,zeta2 double precision :: a1(3),a2(3),d1(3),d2(3) double precision :: pi pi = 3.14159265359d0 alpha = (/0.109818d0, 0.405771d0, 2.22766d0/) d = (/0.444635d0, 0.535328d0, 0.154329d0 /) 10 i=1,3 ...
Read more

queue - mq_receive: message too long -

-
i implementing communication between 2 processes using queue. problem when call function mq_receive, error: message long. i have done following: struct mq_attr attr; long size = attr.mq_msgsize; .... // initializing queue "/gateway" int rc = mq_receive(gateway, buffer, size, &prio); if print size value, size=1, while when print same size program (got same mechanism), not long integer ( -1217186280 )... how can solve error?....so while size = 1, believe it's right "message long" why 1? p.s. have tried put : int rc = mq_receive(gateway, buffer, sizeof(buffer), &prio); but no result. it seems need read docs more carefully. when call mq_receive should pass size of destination buffer. this size must greater mq_msgsize attribute of queue . in addition, seems have error in queue attributes initialisation makes proper mq_receive call impossible. here standard message queue session: fill mq_attr struct ( doc ): struct mq_a...
Read more