On 12-8-2014 19:38, Ben wrote:
I'm trying to solve the following problem in SQLite:
Items are being produced with a short, fixed shelf life. Say 50 days.
Item can be collected every two weeks, where each item must be registered a
week before.
I am trying to create a query where I can list current items and the latest day
they can be registered/collected.
Given the tables:
CREATE TABLE "items" (
"id" INTEGER PRIMARY KEY,
"prod_code" TEXT UNIQUE,
"creation_date" DATE,
"collection_id" INTEGER REFERENCES "collections"("id"), -- for when it's been
booked for collection
)
CREATE TABLE "collections" (
"id" INTEGER PRIMARY KEY,
"book_in_date" DATE,
"collection_date" DATE UNIQUE
)
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.
I think this sort of problem would require a subquery, but I can't seem to pass
the creation_date of an item to a subquery for collections. Should I instead be
processing this in the application rather than database?
Can anyone point me in the right direction for how to approach this kind of
problem?
Thank you,
Ben
untested:
SELECT i.id, prod_code, creation_date,
max(c1.book_in_date) last_book_in_date,
max(c2.collection_date) last_collection_date
FROM items i
INNER JOIN collections c1 ON c1.id=i.id
INNER JOIN collections c2 ON c2.id=i.id
GROUP BY i.id, prod_code, creation_date
to finish you homework, you have to add the 50 day period yourself ;)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
