1

!Modified!

I have a table (mySQL 5.0.x) which contains values for date ranges.

| id | link_id | type_id | value |  start | end    |
====================================================
|  1 |       1 |       1 |    10 | 201111 | 201202 |
|  2 |       1 |       2 |    20 | 201110 | 201201 |
|  3 |       1 |       1 |   100 | 201202 | 201202 |
|  4 |       2 |       1 |    40 | 201202 | 201203 |

where

  • id is the id for record
  • link_id is for linking the data to other tables
  • type_id is for determining the type of the value from other table
  • value is numerical value
  • start and end define the range (real date or integer for year and month)

The trick is that I need to present total value per type and link for a given period on a monthly basis. So the result should be like this for the range 201201 - 201202:

| period | link_id | type_id | value |
======================================
| 201201 |       1 |       1 |    10 |
| 201202 |       1 |       1 |   110 |
| 201201 |       1 |       2 |    20 |
| 201201 |       2 |    NULL |  NULL |
| 201202 |       2 |       1 |    40 |

I could use PHP to put the information like this into the database but there are some drawbacks. I have hundreds of link_ids, many type_id, and the average difference between start and end dates is 30 months so I would have tons of rows.

Peter
  • 351
  • 2
  • 7
  • how start and end are stored, are DATE fields? – vulkanino Feb 09 '12 at 10:13
  • It depends on the solution. They can be some kind of dates or 6 digit integers. I used 6 digit integers because of some mysql _PERIOD_ function but it is not a must. – Peter Feb 09 '12 at 11:17

1 Answers1

0

Here's one way to do it. I've put in $startMonth and $endMonth so you can see how they play in. (I have this niggling feeling this can be simplified, but it currenty escapes me):

SET @MONTH:=$startMonth-1;

SELECT month, link_id, type_id, SUM(value) AS value
FROM (SELECT @MONTH:=@MONTH+1 as month
      FROM foo f
      LIMIT $endMonth-$startMonth+1) a
LEFT JOIN foo f
 ON a.month >= f.start AND a.month <= f.end
GROUP BY link_id, type_id, a.month;

The way it works: suppose you had a table a:

+-------+
| month |
+-------+
|201201 |
|201202 |
+-------+

Then the query you're after is a lot simpler to work out:

SELECT month, link_id, type_id, SUM(value) AS value
FROM a
LEFT JOIN foo
 ON a.month >= f.start AND a.month <= f.end
GROUP BY link_id, type_id, a.month;

Ie you join table a to foo by making sure the month is between the start and end periods, and then you just sum up value, grouping by month, link id, and type id.

So the problem is how to generate table a which contains numbers from $startMonth to $endMonth. Hence the:

SET @MONTH:=201200;
SELECT @MONTH:=@MONTH+1 AS month
FROM foo
LIMIT 2;

This gives a table of numbers startign from 201201 and with 2 rows, ie up to 201202. In this query the FROM foo is dummy - not really used.

mathematical.coffee
  • 55,977
  • 11
  • 154
  • 194
  • Thanks! Almost perfect. Two issues (one is mine). 1) After 201112 the next value is 201113 instead of 201201 but I can handle with a pre-built table. 2) I did not tell that I need to have NULL values for month not having values for the given period. ( **I have modified the question!** ) – Peter Feb 09 '12 at 17:39
  • `SELECT @MONTH:=@MONTH+1 AS month` should be `SELECT @MONTH := PERIOD_ADD( @MONTH , 1 ) AS month` for precise operation and table `foo` has to have at least as many rows as the difference between start and end date. – Peter Feb 09 '12 at 20:35
  • Oh, I knew I was forgetting something with the dates wrapping around. And good point with `foo` needing at least that many rows. I suppose there must be another way to do this. (Why oh why doesn't MySQL have a "SELECT INT i WHERE i BETWEEN xx and yy"?) – mathematical.coffee Feb 09 '12 at 23:30
  • I realized that your solution is what I needed. MySql sometimes behave strange. Anyway thanks for the help it was just great. – Peter Feb 10 '12 at 06:32