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
