|
|
Posted by Erland Sommarskog on 05/22/07 22:00
M@ (mattcushing@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.
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
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|