Ben Sat Ben Sat - 6 days ago 6
MySQL Question

Mysql query on 2 or 3 tables

| ds |
|sales_id| date_issued |
| 1 | 2016-11-30 01:00:00 |
| 2 | 2016-11-30 02:00:00 |
| 3 | 2016-11-30 03:00:00 |

| dsr |
| dsr_id | quantity | date_returned |
| 5 | 1 | 2016-11-30 01:01:00|
| 6 | 1 | 2016-11-30 01:11:00|
| 7 | 3 | 2016-11-30 02:21:00|
| 8 | 1 | 2016-11-30 02:31:00|
| 9 | 2 | 2016-11-30 03:02:00|


How or what query would it be where I could apply this logic
ADD the quantities of dsr WHERE its date_returned is greater than the first date_issued AND less than the following date_issued
that the result would be:

| 2 |
| 4 |
| 2 |


The idea would be something like this:

| dsr |
| dsr_id | quantity | date_returned |
| 5 | 1 | 2016-11-30 01:01:00| --- This 1st and 2nd rows
| 6 | 1 | 2016-11-30 01:11:00| --/ will be added because
the 1st date_issued is
'2016-11-30 01:00:00' >= (the 1st date_returned) < '2016-11-30 02:00:00' which is the following date_issued

| 7 | 3 | 2016-11-30 02:21:00| --- Same idea for this two
| 8 | 1 | 2016-11-30 02:31:00| --/ Since its fits to the condition where this date_returned is just between 2nd & 3rd's date_issued

| 9 | 2 | 2016-11-30 03:02:00|


I know this could be easily done programmatically but I just want to know and learn how to do it in SQL and if it is easier in SQL.

Answer

SQL DEMO

SELECT start_date, end_date, SUM(D.quantity)
FROM (
        SELECT ds1.`sales_id`, 
               ds1.`date_issued` start_date,  
               COALESCE(ds2.`date_issued`, CURRENT_DATE) as end_date
        FROM ds as ds1
        LEFT JOIN ds as ds2
          ON ds1.`sales_id` = ds2.`sales_id` - 1
     ) R
JOIN dsr D
  ON D.`date_returned` >= start_date
 AND D.`date_returned` <  end_date
GROUP BY  start_date, end_date

OUTPUT

enter image description here