Mattia Merlini Mattia Merlini - 5 months ago 8
SQL Question

Creating a log having the date of purchase

I need to create a log having the purchase date of an item.

Items can be owned by only one buyer at time. So, for example, if

item1
was purchased by
buyer2
in 2009 and after by
buyer1
in 2015, then between 2009 and 2015 was owned by
buyer2
.

Here is my table:

+--------+------------+-----------+----------+
| id_doc | date | id_item | id_buyer |
+--------+------------+-----------+----------+
| 11 | 2016-06-07 | 1 | 4 |
| 10 | 2016-06-06 | 1 | 4 |
| 1 | 2015-11-30 | 1 | 1 |
| 9 | 2009-01-01 | 1 | 2 |
| 4 | 2001-01-12 | 1 | 2 |
| 8 | 1996-06-06 | 1 | 2 |
| 3 | 1995-05-29 | 1 | 1 |
| 2 | 1998-05-23 | 2 | 2 |
| 7 | 2014-10-10 | 3 | 2 |
| 6 | 2003-12-12 | 3 | 3 |
| 5 | 1991-01-12 | 3 | 2 |
+--------+------------+-----------+----------+


Here is a kind of table/view I need:

+------------+------------+-----------+----------+--------+
| date_from | date_to | id_item | id_buyer | id_doc |
+------------+------------+-----------+----------+--------+
| 2016-06-07 | - | 1 | 4 | 11 |
| 2016-06-06 | 2016-06-07 | 1 | 4 | 10 |
| 2015-11-30 | 2016-06-06 | 1 | 1 | 1 |
| 2009-01-01 | 2015-11-30 | 1 | 2 | 9 |
| 2001-01-12 | 2009-01-01 | 1 | 2 | 4 |
| 1996-06-06 | 2001-01-12 | 1 | 2 | 8 |
| 1995-05-29 | 1996-06-06 | 1 | 1 | 3 |
| 1998-05-23 | - | 2 | 2 | 2 |
| 2014-10-10 | - | 3 | 2 | 7 |
| 2003-12-12 | 2014-10-10 | 3 | 3 | 6 |
| 1991-01-12 | 2003-12-12 | 3 | 2 | 5 |
+------------+------------+-----------+----------+--------+


I've tried a lot with
GROUP BY
,
GROUP_CONCAT
, trying to access next record date, etc ... but I can't found out how to solve the problem.

Thanks in advance.

Answer

I finally found out the solution only for past purchases.

SELECT 
    main.id_doc, main.id_item, main.date AS "date_from", bi.date AS "date_to", main.id_buyer
FROM 
    MyTable main, MyTable bi
WHERE 
    bi.id_doc = 
    (
        SELECT sub.id_doc 
        FROM MyTable sub 
        WHERE sub.id_item = main.id_item AND sub.date > main.date ORDER BY sub.date ASC LIMIT 1
    );