Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: Companies that have NOT login in last 30 days

    Hi,

    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
    from dgaudit.dgaudit
    where event != 'Web Login'
    and created >= (sysdate) - 30
    group by companyid, created, event
    order by dgaudit.dgaudit.created desc;

    Pleate note the events performed:
    -Login Failed
    -Web Login
    -User Created
    -Web Logout
    -etc...

    I need to display companies that have not logged in at all...
    Please help!

    Thx in advance and I'm very much appreciated...

    thuly2

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "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:
    Code:
    SELECT DISTINCT companyid
    FROM DGAUDIT
    WHERE companyid NOT IN (SELECT companyid FROM DGAUDIT
                            WHERE created > TRUNC(SYSDATE) - 30
                           );

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    Hi Littlefoot,

    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.

    Thx again,
    Ly

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It probably means the query is running slowly. To tune it you may need indexes or you need to rewrite the query. i.e.

    Code:
    SELECT companyid
    FROM DGAUDIT
    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.

    Alan

  5. #5
    Join Date
    Sep 2006
    Posts
    4
    Hi,

    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
    FROM DGAUDIT
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •