Joining on a date range?

[davem99]

Maybe I haven't had enough coffee yet, but......

1) I have a table of documents with creation dates.
2) I have a table of users with unique IDs

I need to grant access to multiple date ranges to users.

So, user 1 may have access to docs created between 9/6-9/10 and 9/20-9/24.

User 2 may have access to docs created between 9/13-9/17.

I'm trying to figure out the best way to represent this in the database so that I can easily determine all the docs that any given user has access to regardless of how many date ranges they may have.

Any suggestions?

thanks!!

 

[NoCoolHandle]

Zoiks

How about adding a DatePermission table and then having one of more lines for each user with a start and end date.

 

[davem99]

That's what I was thinking BUT.....how would I join on that?

Doing it that way would require me to iterate through the rows for that user....

for example....

select * from documents
where creationdate (is somehow in the range of)

10/1/04-10/6/04
10/10/04-10/14/04
etc..etc...

and userid=1

 

[SQLSister]

Either you put in a fields called start range and end range or you put in all the dates. Example of using the first:

Select field1 from table1 join dateRange
on table1.custId = dateRange.custID
where table1.datefield between dateRange.startRange and dateRange.EndRange

Questions about posting. See faq183-874