Welcome to WebmasterWorld Guest from 188.8.131.52
Forum Moderators: open
200509 - 2
200510 - 40
200512 - 4
200601 - 32
200602 - 12
The problem here is the missing entry for "200511". It's missing because I don't have any data on that month, but in those cases I would like a row to be returned like "200511 - ". Is this possible? I remember someone showed me a while ago that you could create a view with dates and join with that in some way? Is that possible? Is there an easier way? (ok, enough questions ;) )
By the way, I'm on MySQL 4.
On first thought, without much thought, here's my solution:
one table with nothing but dates - 1 column with as many dates as you want to compare. This table can be dynamic, rolling over every month with new dates, etc.
Then, an outer join to the data table. If there is no data in the data table, you'll still get a row for each date.
You could even create a temp table on the fly in your code with the dates, then DROP it after your outer join returns results.
1. Open your query recordset (with dates missing)
2. Begin simple loop to display the date range (200509 to 200602)
3. Check whether the current record's date value is the same as the current loop date value:
- If [recordset date] = [code loop date], display the recordset value and move to the next record.
- If not, just display blank/zero/whatever