Обсуждение: date range query help

Hi,

We have two tables.

select * from
mobile_custodian;

 custodian_id | user_id | issue_date | return_date |  mobile_no
--------------+---------+------------+-------------+-------------
            4 |     Ben | 2008-10-11 | 2008-10-13  | 09455225998
            5 |    Josh | 2008-10-15 |             | 09455225998
(2 rows)


select * from
call;

 call_id |      datetime       |  mobile_no  | charge
---------+---------------------+-------------+--------
       2 | 2007-10-14 13:27:00 | 09455225998 |    5.2
       1 | 2007-10-12 10:00:00 | 09455225998 |    4.5
(2 rows)



Now user Ben has passed his mobile to user Josh and we issued Josh his
mobile on 2008-10-15.

1. Is it possible for me to write a query that will have the fields

call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no
call.charge

that will use call.datetime and lookup the date range from
mobile_custodian.issue_date and mobile_custodian.return_date to
identify the right user for each call?

2.  Do I need to change the issue_date & return_date fields to
timestamp to perform the above?


Thanks.

2008/11/20 Adam Rich <adam.r@sbcglobal.net>:
>> Now user Ben has passed his mobile to user Josh and we issued Josh his
>> mobile on 2008-10-15.
>>
>> 1. Is it possible for me to write a query that will have the fields
>>
>> call.call_id,
>> call.datetime,
>> mobile_custodian.user_id,
>> call.mobile_no
>> call.charge
>>
>> that will use call.datetime and lookup the date range from
>> mobile_custodian.issue_date and mobile_custodian.return_date to
>> identify the right user for each call?
>>
>> 2.  Do I need to change the issue_date & return_date fields to
>> timestamp to perform the above?
>>
>
> No, a date will work fine.  Try this:
>
>
> select call.call_id,
>  call.datetime,
>  mobile_custodian.user_id,
>  call.mobile_no
>  call.charge
> from call, mobile_custodian
> where call.mobile_no = mobile_custodian.mobile_no
> and call.datetime between mobile_custodian.issue_date
>        and mobile_custodian.return_date

sorry I get nothing :(

2008/11/20 brian <brian@zijn-digital.com>:
> novice wrote:
>>
>> 2008/11/20 Adam Rich <adam.r@sbcglobal.net>:
>>>
>>> select call.call_id,
>>>  call.datetime,
>>>  mobile_custodian.user_id,
>>>  call.mobile_no
>>>  call.charge
>>> from call, mobile_custodian
>>> where call.mobile_no = mobile_custodian.mobile_no
>>> and call.datetime between mobile_custodian.issue_date
>>>       and mobile_custodian.return_date
>>
>> sorry I get nothing :(
>>
>
> How about:
>
> SELECT call.call_id,
> call.datetime,
> mobile_custodian.user_id,
> call.mobile_no,
> call.charge
> FROM call
> LEFT JOIN mobile_custodian
> ON call.mobile_no = mobile_custodian.mobile_no
> AND call.datetime
>  BETWEEN
>  mobile_custodian.issue_date
>  AND
>  mobile_custodian.return_date;
>

this gave me nothing on the user_id field  :(

 call_id |      datetime       | user_id |  mobile_no  | charge
---------+---------------------+---------+-------------+--------
       1 | 2007-10-12 10:00:00 |         | 09455225998 |    4.5
       2 | 2007-10-16 13:27:00 |         | 09455225998 |    5.2

On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
> sorry I get nothing :(

Of course not. None of the dates you gave in the example overlap.

2008/11/20 Rodrigo E. De León Plicet <rdeleonp@gmail.com>:
> On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
>> sorry I get nothing :(
>
> Of course not. None of the dates you gave in the example overlap.
>

But it should still have the 1st entry with the name Ben?  Am I
missing something?

novice wrote:
> 2008/11/20 Rodrigo E. De León Plicet <rdeleonp@gmail.com>:
>> On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
>>> sorry I get nothing :(
>> Of course not. None of the dates you gave in the example overlap.
>>
>
> But it should still have the 1st entry with the name Ben?  Am I
> missing something?
>



Yes, you are missing something.  You would only get 1st entry with the
name Ben if the dates in two tables were in the same year.