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
|
