sql server - SQL property from inherited Parent-Child relationship -

your inputs appreciated..!! please have , suggest..

two tables -

table1

user(pk) | permission_on usr1     | folder_a usr2     | folder_b usr3     | folder_c 

and

table2

rowaddedtimestamp(pk) | user(pk) | substitute_user | begindate | enddate xxxx1                 | usr1     | usr1            | 1-1-2010  | 1-1-2010 xxxx2                 | usr2     | usr2            | 1-1-2010  | 1-1-2010 xxxx3                 | usr1     | usr2            | 1-1-2010  | 1-1-2012 xxxx4                 | usr3     | usr3            | 1-1-2010  | 1-1-2010 xxxx5                 | usr2     | usr3            | 1-1-2011  | 1-1-2013 

i have tried not getting how exact permission on particular user consideration of substitute user.

like if today -

2-2-2011 permission on usr3 - folder_a, folder_b, folder_c

folder_c  -- direct permission usr3 folder_b  -- usr3 substitute of usr2 (between 1-1-2011  1-1-2013) folder_a  -- usr2 substitute of usr1 (between 1-1-2010  1-1-2012) 

2-2-2012 permission on usr3 - folder_b, folder_c

folder_c  -- direct permission usr3 folder_b  -- usr3 substitute of usr2 (between 1-1-2011  1-1-2013) folder_a  -- **** no because usr2 substitute of usr1 (between 1-1-2010  1-1-2012) , today 2-2-2012 

2-2-2013 permission on usr3 - folder_c

folder_c  -- direct permission usr3 folder_b  -- **** no because usr3 substitute of usr2 (between 1-1-2011  1-1-2013)  , today 2-2-2013 folder_a  -- **** no because usr2 substitute of usr1 (between 1-1-2010  1-1-2012) , today 2-2-2013 

use mssql functions. drastically improve performance comparing nested , recursive queries.

e.g. function:

create function getuserlist (@inputuser varchar(250), @inputdate varchar(250)) returns @userlist table    (     username   varchar(250),    ) begin    insert @userlist         select a.substitute_user,         table2         a.user = @inputuser         , a.begindate <= @inputdate         , a.enddate >= @inputdate    return end 

and afterwards use source substitute user list e.g.

select permission_on table1 user in (select username table1('usr1','2-2-2011')) or user = 'usr1' 

--edit--

ohh, see, misunderstood user substitute user.. in such case can use sql cte functionality:

take @ sqlfiddle

in query should replace user , both dates in query

with accesshierarchy ( userr ) (    -- base case    select       userr    table1    userr = 'usr3'     union     -- recursive step    select       e.userr    table2 e       inner join accesshierarchy eh on          e.substitute_user = eh.userr       e.userr != e.substitute_user       , begindate <= '2011-05-05'       , enddate >= '2011-05-05' )  select permission_on table1 userr in (select userr accesshierarchy ) 

and can create test tables statement:

create table table1      (      userr varchar(255) primary key,       permission_on varchar(255)     );  insert table1 (userr, permission_on) values ('usr1', 'fola'), ('usr2', 'folb'), ('usr3', 'folc');    create table table2     (      userr varchar(255),       substitute_user varchar(255),       begindate datetime,       enddate datetime,      );  insert table2 (userr, substitute_user, begindate, enddate) values ('usr1', 'usr1', '2010-01-01', '2010-01-01'), ('usr2', 'usr2', '2010-01-01', '2010-01-01'), ('usr1', 'usr2', '2010-01-01', '2012-01-01'), ('usr3', 'usr3', '2010-01-01', '2010-01-01'), ('usr2', 'usr3', '2011-01-01', '2013-01-01');