Unanswered: Companies that have NOT login in last 30 days
I'm new to the forum and need your help in displaying companies that have NOT login in last 30 days...
This is what I have so far...however, companyid is still not grouped...
select companyid, to_char(created,'DD-MON-YY') created, event
where event != 'Web Login'
and created >= (sysdate) - 30
group by companyid, created, event
order by dgaudit.dgaudit.created desc;
Pleate note the events performed:
I need to display companies that have not logged in at all...
"created" is a DATE column and it contains date and time when that particular "companyid" logged in, right? If so, all you need is "companyid" - there's no reason to select "created" or "event" when nothing happened. Therefore, something like this might help:
SELECT DISTINCT companyid
WHERE companyid NOT IN (SELECT companyid FROM DGAUDIT
WHERE created > TRUNC(SYSDATE) - 30
Thank you for your reply and for your help, you are right about not needing to display event or created columns, I've tried your query several times and it just hangs for some reason...unfortunately there are no error messages/results.
It probably means the query is running slowly. To tune it you may need indexes or you need to rewrite the query. i.e.
group by companyid
having max(created) <trunc(sysdate)-30
You need to try different versions of the query including Littlefoots to see which one works best in your production environment. You may find an index on created,companyid would help both queries. Turn on tracing and find out which query does the least amount of work.
I've sort of combined the queries, set conditions to limit records, and came up with this...
select s.id, initcap(name) Name, trunc(a.created) Created
from companies s, dg_companyprops c,
(SELECT companyid, max(created) created
WHERE event = 'Web Login'
AND created >= TRUNC(SYSDATE) - 60
group by companyid) a
where s.id = a.companyid (+)
and s.id = c.id
and (a.created <= trunc(sysdate) -30 or a.created is null)
order by 1;
Thx AlanP and Littlefoot for your input...much appreciated