dimanche 19 juin 2016

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.

Aucun commentaire:

Enregistrer un commentaire