SQL Period between two dates, query for every single day's average

Question

I have several entries saved as follows:

ID | StartDate | Enddate | Amount

I do now want to query for every single day in this period to part the total amount between those dates.

For example

1 | 2012-01-01 | 2012-01-05 | 10
2 | 2012-01-04 | 2012-01-05 | 20

should become

2012-01-01 |  2
2012-01-02 |  2
2012-01-03 |  2
2012-01-04 | 12
2012-01-05 | 12

Is this possible? I cannot imagine how toconclude from the period to the single day values. Thanks in advance!

Answer 1

When you need query result with all dates is necessary to have Dates table (little help). I give you answer and without using Date Table.

SQLFIDDLEExample 1 with Date Table

Query:

SELECT 
DATE_FORMAT(td.TimeKey, '%Y-%m-%d')  as Date,
SUM(Amount/(SELECT COUNT(tdc.TimeKey) 
       FROM Table1 t1c, TDate tdc
       WHERE t1c.StartDate<= tdc.TimeKey
          AND t1c.Enddate >= tdc.TimeKey
          AND t1c.ID = t1.ID )) as Total_Amount
FROM Table1 t1, TDate td
WHERE t1.StartDate<= td.TimeKey
  AND t1.Enddate >= td.TimeKey
GROUP BY Date
ORDER BY Date

Result:

|       DATE | TOTAL_AMOUNT |
-----------------------------
| 2012-01-01 |            2 |
| 2012-01-02 |            2 |
| 2012-01-03 |            2 |
| 2012-01-04 |           12 |
| 2012-01-05 |           12 |

And same Result without using date table:

SQLFIddle example just with SQL (without Date table)