Re: [SQL] [GENERAL] date range query help

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: [SQL] [GENERAL] date range query help
Date: 2008-11-20 04:19:05
Message-ID: 4924E539.90603@sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice pgsql-sql

novice wrote:
> 2008/11/20 brian <brian(at)zijn-digital(dot)com>:
>> novice wrote:
>>> 2008/11/20 Adam Rich <adam(dot)r(at)sbcglobal(dot)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
>

This is the expected result given the sample data you provided. If your
sample data for call table should be 2008 instead of 2007; then output
would be:

call_id | datetime | user_id | mobile_no | charge
---------+---------------------+---------+-------------+--------
2 | 2008-10-14 13:27:00 | | 09455225998 | 5.2
1 | 2008-10-12 10:00:00 | Ben | 09455225998 | 4.5

user_id is still empty on call_id=2 because neither custodian had the
mobile on Oct.14!

In response to

Browse pgsql-general by date

  From Date Subject
Next Message novice 2008-11-20 04:32:53 Re: [SQL] date range query help
Previous Message Rodrigo E. De León Plicet 2008-11-20 04:17:18 Re: [SQL] date range query help

Browse pgsql-novice by date

  From Date Subject
Next Message novice 2008-11-20 04:32:53 Re: [SQL] date range query help
Previous Message Rodrigo E. De León Plicet 2008-11-20 04:17:18 Re: [SQL] date range query help

Browse pgsql-sql by date

  From Date Subject
Next Message novice 2008-11-20 04:32:53 Re: [SQL] date range query help
Previous Message Rodrigo E. De León Plicet 2008-11-20 04:17:18 Re: [SQL] date range query help