var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: help with date range query please
i have a trouble writing a date range query. here's what i have:
table activity (
table event (
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.
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
... 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.
I think this will do the trick
select * from
where e.purchase_date between a.active_date and a.deactive_date
and e.user_id = a.user_id
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.
I get you now.
The trick to do it is to
select all purchases for a user
select all purchases for a user within start and end dates (as in the query I supplied previously).