I have 2 tables, one is a list of customers, the other a list of historic and future delivery dates for these customers.
Convenience code to create example table:
CREATE TABLE Customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
INSERT INTO Customers (name) VALUES ('David');
INSERT INTO Customers (name) VALUES ('John');
INSERT INTO Customers (name) VALUES ('Anne');
CREATE TABLE Orders (
id INTEGER,
item TEXT,
delivery_date TEXT
);
INSERT INTO Orders VALUES ( 1, 'gopro' , '2016-04-05');
INSERT INTO Orders VALUES ( 1,'car', '2015-12-30');
INSERT INTO Orders VALUES ( 1,'watch', '2015-10-20');
INSERT INTO Orders VALUES ( 2, 'laptop', '2016-04-15');
INSERT INTO Orders VALUES ( 3, 'car', '2016-01-15');
INSERT INTO Orders VALUES ( 3,'cup', '2015-11-30');
I need to create a SQL statement that shows all customers and their most recent and next delivery date.
(Something like - SELECT * from Customers with recent and next item and date FROM Orders.)
Expected Result Table
Name, Most Recent Item, Recent Date, Next Item, Next Date
David, car, 2015-12-30, gopro, 2016-04-05
John, NULL, NULL, laptop, 2016-04-15
Anne, car, 2016-01-15, NULL, NULL
But the closest I can get with a single statement is:
SELECT
(SELECT o.item from Orders WHERE delivery_date < date('now') ORDER BY delivery_date DESC LIMIT 1) as Last_Item_Ordered,
(SELECT o.delivery_date from Orders WHERE delivery_date < date('now') ORDER BY delivery_date DESC LIMIT 1) as Last_Delivery_Date,
(SELECT o.item from Orders WHERE delivery_date >= date('now') ORDER BY delivery_date ASC LIMIT 1) as Next_Item_Ordered,
(SELECT o.delivery_date from Orders WHERE delivery_date >= date('now') ORDER BY delivery_date ASC LIMIT 1) as Next_Delivery_Date,
c.*
FROM (Orders o INNER JOIN Customers c ON c.id = o.id)
GROUP BY c.name
ORDER BY c.id;
and the Actual results are as this:
Last_Item_Ordered Last_Delivery_Date Next_Item_Ordered Next_Delivery_Date id name
watch 2015-10-20 watch 2015-10-20 1 David
laptop 2016-04-15 laptop 2016-04-15 2 John
cup 2015-11-30 cup 2015-11-30 3 Anne
Todays date is 21st February 2016, hence some values are expected to be NULL for the above customers future results.
If anyone can help, be greatly appreciated. thanks
Aucun commentaire:
Enregistrer un commentaire