PHP and MySql with date problem...?

Question

In my project I am using a table named "Compensation" like.....

+--------------------------------------------------------------+
| Id  |  receiver_Id   |  compensation   |      date           | 
|--------------------------------------------------------------|
| 1   |  5             |  50%            | 2011-02-15 12:15:00 | 
| 2   |  3             |  40%            | 2011-04-05 18:35:00 |
| 3   |  3             |  30%            | 2011-04-25 06:24:00 | 
| 4   |  5             |  45%            | 2011-04-21 19:05:00 |
| 5   |  5             |  60%            | 2011-04-30 12:05:00 |
.......................

Here the date represents that the compensation is changed on that particular date. For receiver 5, the compensation is 50%before Feb 15 2011. And the compensation is45%fromthe date15 Feb 2011 12:15:01 to 21 April 2011 19:05:00`. And so on....

Here When I create invoice for the month APRIL 2011 for the receiver 5, I have to use the compensation as 45% till date 21 April 2011 and for 22 April 2011 to 30 April 2011 I have to use 60% as compensation...

But How to get the compensation for a month or between 2 dates since the compensation may be modified multiple times in a month as id 4 and 5 shows.......

Please help me writing SQL for the above OR I have to make changes on the table structure to make it simple......?

Thanks in advance

Answer 1

This would be much easier when your table tracked both valid-from and valid-to dates:

+--------------------------------------------------------------+---------------------+
| Id  |  receiver_Id   |  compensation   |      date_from      |      date_to        |
|--------------------------------------------------------------|---------------------|
| 1   |  5             |  50%            | 2011-02-15 12:15:00 | 2011-04-21 19:05:00 | 
| 2   |  3             |  40%            | 2011-04-05 18:35:00 | 2011-04-25 06:24:00 | 
| 3   |  3             |  30%            | 2011-04-25 06:24:00 | 0000-00-00 00:00:00 | 
| 4   |  5             |  45%            | 2011-04-21 19:05:00 | 2011-04-30 12:05:00 | 
| 5   |  5             |  60%            | 2011-04-30 12:05:00 | 0000-00-00 00:00:00 |
.......................

Then you can query:

SELECT * FROM compensations
    WHERE (date_to > $start_of_month OR date_to = 0 )
    AND date_from < $end_of_month;

Answer 2

the first sql will fetch the current month/year

the 2nd sql shows it hardcoded with a specific month/year

SELECT * FROM compensations
    WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW());


SELECT * FROM compensations
    WHERE YEAR(date) = '2011' AND MONTH(date) = '4';