how to create date table for comparison reporting

[JRN30]

Does anyone have a recommendation on the best way to create a table using dates. This is a custom table we are building in mysql. From a reporting stand point, the data will need to be compared by this year to last year and this month to last month (quarter to quarter, etc.). so a typical report would look like:
credit code 2009 goal 2008 goal
A 20 25
B 10 15

This would be a ytd example. Currently the date data is in one field called date and looks like:
YTD2009
YTD2008
Jan2008
Jan2009

This is not giving me my nice comparison on a report because it is in the table with 2009 listed first and 2008 after, giving:

credit code 2009 goal 2008 goal
A 20
B 10
A 25
B 15

Any advice on best way to set up dates in a custom table in mysql that would be tied into other tables to be able to report on comparisons by dates: months, quarters, years?

thanks,
Jennifer

 

[Hans63]

When reporting with SQL I would look into the CASE statement. The precise syntax for MySQL i don't know.

this is the Oracle syntax:
select credit_code
, sum(case when date='jan2008'
then value
else 0
end) as jan2008,
, sum(case when date='jan2009'
then value
else 0
end) as jan2009,
where date like 'jan%'
group by credit_code

This is however hard coded and it may require some more tweaking to get it generic. e.g. by using variables.

 

[johnherman]

You could put an index on the column and when doing a SELECT, order by the column DESC. The default order is ASC.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright