0

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!

thoughtgap
  • 51
  • 2
  • 10
  • 1
    What database and version are you using? – Mark Byers Nov 14 '12 at 18:57
  • I am running a MySQL database "mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $". I would be happy about a general answer though, so I can imagine how that "parting" of the pariod could work. Thanks! – thoughtgap Nov 14 '12 at 19:01

2 Answers2

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)

Community
  • 1
  • 1
Justin
  • 9,634
  • 6
  • 35
  • 47
0

You might consider using an integer table to create your date ranges. See my answer here for an idea of what I'm talking about.

In your case you'd determine the count of days in each date range and use that for your maximum integer. That max integer would be both to limit the number of rows to the appropriate number of days and to calculate the average for each day in the date range.

Community
  • 1
  • 1
dnagirl
  • 20,196
  • 13
  • 80
  • 123
  • Thank you for this suggestion. I will take a look at your solution later, but I am afraid I cannot change the db-table's structure. I will maybe have to do these calculations in php and fire several queries (one per day). – thoughtgap Nov 14 '12 at 20:58
  • @thoughtgap: you don't need to change any table structure. You'd just need to add a single one column table called `numbers`. If that's not possible, you could even put `nums` in another db and then join to it like this `FROM mytable JOIN otherdb.nums` – dnagirl Nov 15 '12 at 12:17
  • Oh, I got your point. Thanks for this suggestion, I will definately try this out later for learning purposes. For instant success I prefer the solution below. Have a nice day! – thoughtgap Nov 15 '12 at 18:15