Welcome to WebmasterWorld Guest from 54.161.157.73

Forum Moderators: open

Message Too Old, No Replies

Map query results to a "calendar"

Return blank spots as well

     
7:39 am on Sep 27, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:July 18, 2005
posts:157
votes: 0


Hi, I'm doing a query that very basically does something like this: SELECT min(somefield) FROM TABLE GROUP BY date_format(fieldwithdate, '%Y%m'); Which returns the smallest "somefield" each month (type timestamp). Sometimes it returns something like

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.

1:35 pm on Sept 27, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
posts:4392
votes: 0


That's a tricky/juicy puzzle.

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.

Interesting one.

11:40 am on Sept 28, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:July 18, 2005
posts:157
votes: 0


Hmm, think I'll try the solution with a table of dates. Thanks!
11:44 am on Sept 28, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 29, 2002
posts:980
votes: 0


An alternative way of doing this is to handle the date range in the presentation code. I've done a similar thing with an event calendar.

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
4. Loop

HTH...

2:21 pm on Sept 28, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:July 18, 2005
posts:157
votes: 0


I thought of doing that first, but since I'm processing my data in XSLT I thought it might be more efficient to have the data be "correct". But I'll look into looping and such in the php code that generates the XML file instead.
12:31 pm on Sept 29, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2162
votes: 0


I dont know if anyone has mentioned this already ,sorry.. but the solution is a pivot/crosstab
7:41 am on Sept 30, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:July 18, 2005
posts:157
votes: 0


aspdaddy: Care to give me some more info on it?
2:02 pm on Sept 30, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2162
votes: 0


I am assuming you want to create a column in the output for each day? I do like the suggestion to OUTER JOIN a Dates table. The crosstab will work too - see below.

There is some decent free code for SQL2K for Dynamic Cross-Tabs/Pivot Tables [sqlteam.com]. The The Rozenshtein Method [stephenforte.net] is a common technique if you know how many columns you need.