Padding a query with months/weeks/quarters with no data

[elbojpb]

When retrieving monthly data from any datasource, how do CF experts (of which I am not one) create results for time intervals in which there are no results? I.E. Pulling event data by month, how do I show that there was no activity for a given month? Thanks in advance.

[Dan_Bracuk]

depends on your db and table structure.

[dempster]

It depends on what you use to control your output. If I wanted to generate a report for a calendar year, I could create a loop that goes through the 12 months to show activity for each month:

<CFSET rptyear=2006>
<CFLOOP INDEX="mo" FROM="1" TO="12">
#MonthAsString(mo)# #rptyear#<BR>

[do your query here]
</CFLOOP>

Inside that loop, you can query the DB for matching records, and if there are none, you can show that.

[elbojpb]

Thanks ... that's exactly what I had been doing, but thought that there may be some discreet CF function that would pad results with place-holder records for months with missing data.

[Dan_Bracuk · Accepted solution]

There is a way to make dempster's approach more efficient.

Step 1 - run your query from the db. (query 1)
Step 2 - Build a cold fusion query to get all the year-months in the date range of interest (query 2)
Step 3 - Do a query of queries to get all the year-months in query 2 that are not in query 1. (query 3)
Step 4 - Do a query of queries that unions queries 1 and 3.

[elbojpb]

Aaaahhhh ... {light goes on}. That certainly will cut down on the number of queries. Many many thanks!