Posted by M@ on 05/25/07 15:48

On May 22, 6:00 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> M@ (mattcush...@gmail.com) writes:
> > I have about 14 tables that I am trying to tie together in a view to
> > see a user's status in our company.
>
> > Joe User:
> > Email - Active
> > ADP - Active
> > Portal - Inactive
> > etc.
>
> > We need to track application access across the company, so we have
> > tables for each application that contain user info(username,
> > password(encrypted), start date, end date, changed date) so that we
> > can track who has what, and when they were given access as well as
> > when it was taken away.
>
> Why is there one table per application? Are there other columns that
> you did not mention that are different from table to table? For this
> problem at least, it would have been easier, if had been one table.

Yeah, sorry. Each App has 4 fields in common (UserID, StartDate,
EndDate, Password), but can also have quite a few other fields
associated with the application access.

> But you could define a view:
>
> SELECT App = 'Email', UserID, StartDate, EndDate, Password, ...
> FROM Email
> UNION ALL
> SELECT 'ADP', UserID, StartDate, EndDate, Password, ...
> FROM ADP
> UNION ALL
> ...
>
> > What I would like to do is to take the userID field and look for their
> > application access across the company. To do this, i'll have to look
> > for the max value in each table because someone could be given access,
> > have it taken away, and be given it again. People move all over the
> > place here, so we have to be able to track who has what, when, and at
> > what building.
>
> I'm not sure really what you are looking for, but to see the records with
> the most recent StartDate for each application and user, you could do:
>
> SELECT A.*
> FROM AllApps A
> JOIN (SELECT App, UserID, MaxStartDate = MAX(StartDate)
> FROM AllApps
> GROUP BY App, UserID) AS M ON a.App = M.App
> AND a.UserID = M.UserID
> AND a.StartDate = M.MaxStartDate

Thanks, I'll try it out.

[Back to original message]