Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how to create date table for comparison reporting

how to create date table for comparison reporting

how to create date table for comparison reporting

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:

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?



RE: how to create date table for comparison reporting

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.

RE: how to create date table for comparison reporting

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close