Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: help with date range query please

    hi all,
    i have a trouble writing a date range query. here's what i have:

    table activity (
    user_id integer,
    active_date date,
    deactive_date date)

    table event (
    ...
    user_id integer,
    purchase_date date,
    ...)

    activity table can contain multiple activation/deactivation events for a given user. i'd like to find the way of writing a SQL query to find out whether a user with a given user id purchased anything not between their active/deactive period.

    for example,
    activity:
    123 10/11/2003 20/11/2003
    123 01/12/2003 31/12/2003
    123 01/01/2004 02/02/2004
    123 29/02/2004 31/03/2004

    event:
    ... 123 15/01/2004 ...
    ... 123 23/02/2004 ...
    ... 123 27/03/2004 ...

    i expect the query to return "123 23/02/2004" record as the event falls between the time when a user was activated/deactivated.

    i use Oracle 8 and the only way i can see it so far is to use PL-SQL with cursor etc, however i'd like to use standard SQL.

    any help would be appreciated.

    thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think this will do the trick

    select * from
    activity a,
    event e
    where e.purchase_date between a.active_date and a.deactive_date
    and e.user_id = a.user_id

  3. #3
    Join Date
    Mar 2004
    Posts
    2


    of course it won't. you've probably misunderstood the problem. i'll draw it here:

    say we've got 4 time intervals when a user was active:
    1|_____|2 3|_____|4 5|_____|6 7|_____|8
    and a number of purchase events that fall between:
    1&2, 4&5, 5&6, 7&8

    i'd like to find out if anything was purchased between them, ie. 2&3, 4&5 and 6&7. for the example above the query would return 4&5.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I get you now.

    The trick to do it is to

    select all purchases for a user
    minus
    select all purchases for a user within start and end dates (as in the query I supplied previously).


    Alan

Posting Permissions

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