Operating System - HP-UX
1748169 Members
4007 Online
108758 Solutions
New Discussion юеВ

Pull latest entry in table

 
SOLVED
Go to solution
Ratzie
Super Advisor

Pull latest entry in table

I have a script that sources two tables and pulls id's that have not logged in, in 90 days.

SELECT empid
FROM v_emp
WHERE empid NOT IN (SELECT empid
FROM v_emphist
WHERE logon_time > SYSDATE - 90)

EMPID
----------
BRIDER
DFLINSTONE
DSMITH
JREEVES
4 rows selected

What I would like to do is also pull the last login time along with the empid.
But...
v_emphist holds all login times.
I have two tables one holds the empid and other data, the other table holds empid and login times.


But the data looks like this:
emp_hist table
EMPID LOGON_TIME
---------- --------------------
ABACUS 5/5/2006 8:04:44 AM
ABACUS 5/4/2006 7:00:33 AM
ABACUS 5/3/2006 7:32:37 AM
ABACUS 5/2/2006 7:01:51 AM
ABACUS 5/1/2006 8:11:48 AM
ABOLES 4/26/2006 7:15:14 AM
ABOLES 4/25/2006 10:05:43 AM
ABOLES 4/25/2006 8:40:18 AM
ABOLES 4/25/2006 8:06:53 AM
And so on for all id's
7 REPLIES 7
Patti Johnson
Respected Contributor

Re: Pull latest entry in table

Try this.
SELECT empid, max(b.logon_time)
FROM v_emp a, v_emphist b
WHERE a.empid NOT IN (SELECT empid
FROM v_emphist
WHERE logon_time > SYSDATE - 90)
and a.empid = b.emp_id

Patti
TwoProc
Honored Contributor
Solution

Re: Pull latest entry in table

I agree with Patti, but "NOT IN" runs slowly.

Try this instead, it should run substantially faster.

SELECT empid, max(b.logon_time)
FROM v_emp a, v_emphist b
WHERE 0 =
(
SELECT count(*)
FROM v_emphist c
WHERE .empid = a.empid
AND logon_time > SYSDATE - 90
)
and a.empid = b.emp_id;
We are the people our parents warned us about --Jimmy Buffett
Ratzie
Super Advisor

Re: Pull latest entry in table

I have reduced to bare script from one table and it complains with the first empid about not a single group group function.

SELECT empid, max(logon_time)
FROM v_emphist
WHERE 0 =
(
SELECT count(*)
FROM v_emphist
WHERE logon_time > SYSDATE - 90
)
TwoProc
Honored Contributor

Re: Pull latest entry in table

Try adding the bottom line like below:

SELECT empid, max(logon_time)
FROM v_emphist
WHERE 0 =
(
SELECT count(*)
FROM v_emphist
WHERE logon_time > SYSDATE - 90
)
group by empid;
We are the people our parents warned us about --Jimmy Buffett
TwoProc
Honored Contributor

Re: Pull latest entry in table

Oh, I see you took out a join from the sub-select; you'll need to put it back otherwise, you'll not get the results you're wanting:

Try the following:

SELECT empid, max(logon_time)
FROM v_emphist a
WHERE 0 =
(
SELECT count(*)
FROM v_emphist b
WHERE b.empid=a.empid
AND logon_time > SYSDATE - 90
)
GROUP BY empid;
We are the people our parents warned us about --Jimmy Buffett
Ratzie
Super Advisor

Re: Pull latest entry in table

Something is wonky, because the last entry returns no results.

But if I run this, I get 171 records returned.

SELECT empid
FROM v_emp
WHERE empid NOT IN (SELECT empid
FROM v_emphist
WHERE logon_time > SYSDATE - 90)
Ratzie
Super Advisor

Re: Pull latest entry in table

appreciate the help