Thanks for the advice everyone, I'd not considered the group by clause here.
The following seems to work so far:
SELECT items.id,
items.prod_code,
items.creation_date,
collections.book_in_date as last_book_in_date,
collections.collection_date as last_collection_date
FROM items, collections
WHERE collections.collection_date <= date(items.creation_date, '+50 days')
GROUP BY items.id
ORDER BY prod_code ASC,
last_collection_date ASC
Thanks again,
Ben
On 13 Aug 2014, at 02:43, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> I don't think you want max() around collections.book_in_date. You want the
> max(collection_date) but the book_in_date from that row. Since the
> collection_date is unique, the book_in_date can only come from one record.
>
>> -----Original Message-----
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Petite Abeille
>> Sent: Tuesday, 12 August, 2014 12:15
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite relative dates and join query help
>>
>>
>> On Aug 12, 2014, at 7:38 PM, Ben <sqlite_l...@menial.co.uk> wrote:
>>
>>> The result I'm after is:
>>>
>>> id, prod_code, creation_date, last_book_in_date, last_collection_date
>>>
>>> Where the final two columns are from the collection which is the
>> farthest in the future, but still within the 50-day period from creation.
>>
>> Perhaps something along these lines:
>>
>> select items.id,
>> items.prod_code,
>> items.creation_date,
>> max( collections.book_in_date ) as last_book_in_date,
>> max( collection_date ) as last_collection_date
>> from items
>>
>> join collections
>> on collections.id = items.collection_id
>>
>> where collections.book_in_date between items.creation_date and
>> items.creation_date + 50
>> and collections.collection_date between items.creation_date and
>> items.creation_date + 50
>>
>> group by items.id,
>> items.prod_code,
>> items.creation_date
>>
>> N.B.
>>
>> There is no 'date' type in SQLite. Will assume something else, say, a
>> Julian number for ease of date manipulation.
>>
>>> Should I instead be processing this in the application rather than
>> database?
>>
>> No. A database is the perfect place to process data.
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
