topic Re: Pull latest entry in table in Operating System - HP-UX

Pull latest entry in table

Ratzie — Fri, 12 May 2006 09:48:48 GMT

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

Re: Pull latest entry in table

Patti Johnson — Fri, 12 May 2006 09:59:40 GMT

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

Re: Pull latest entry in table

TwoProc — Fri, 12 May 2006 13:04:05 GMT

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;

Re: Pull latest entry in table

Ratzie — Fri, 12 May 2006 13:27:18 GMT

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
)

Re: Pull latest entry in table

TwoProc — Fri, 12 May 2006 13:53:47 GMT

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;

Re: Pull latest entry in table

TwoProc — Fri, 12 May 2006 13:56:16 GMT

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;

Re: Pull latest entry in table

Ratzie — Mon, 15 May 2006 17:16:56 GMT

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)

Re: Pull latest entry in table

Ratzie — Thu, 23 Nov 2006 21:39:11 GMT

appreciate the help