Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novicepgsql-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

pgsql-novice by date

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

pgsql-general by date

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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group