view of max values from many tables

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.

Every application table has a userID attached to it.

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 started out with trying to left outer join the tables together, but
it didn't work. I tried doing something along the lines of:
select
esarfAppEmail.emailID,
esarfAppEmail.esarfUserID,
CASE WHEN esarfAppEmail.endDate IS NULL Then 'Active' else 'Inactive'
end as EmailStatus--,
>From esarfAppEmail LEFT OUTER JOIN
esarfUser on esarfAppEmail.esarfUserID = esarfUser.userID
where emailID in
(select max(esarfAppEmail.emailID)
from esarfAppEmail
group by esarfAppEmail.esarfUserID)

And this works, but only if I run it for the Email application. As
soon as I start adding applications, the results go down.

Am I headed in the right direction, or completely off base?

Re: view of max values from many tables

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: view of max values from many tables

On May 22, 6:00 pm, Erland Sommarskog 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.