Adam Rich wrote:
>> 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
>
>
>
Here's my version of the query which takes into account calls that are made with the current custodian, therefore the return_date field is null (sorry for the lack of formatting):
select c.call_id, c.datetime, mc.user_id, c.mobile_no, c.charge from mobile_custodian mc
inner join call c using (mobile_no) where (c.datetime between mc.issue_date and
mc.return_date) or (c.datetime > mc.issue_date and mc.return_date is null)
Raymond