dimanche 21 février 2016

SQLite conditional SELECT statement - multiple criteria but single statement

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