Coldfusion, outputting SQL results grouped by Date Grouping, Today, Yesterday, This Week, etc...

Question

Hello! I'm hitting the DB for a 100 records from a MySQL DB that have a DateAdded (timestamp) column.

I'd like to find a SMART way to display the records as follows in Coldfusion, w/o hitting the database multiple times.

Today: - records..... Yesterday: - records..... Earlier This Week: - records..... Earlier This Month: - records..... Older: - records.....

Thanks for the ideas on how to get this done smartly in ColdFusion.

Answer 1

multiple db calls would have a pretty negligible cost in most apps

but, this can all be done in one call, if you selet all the records and order by time desc.

end the current section when the time switches (when it becomes older than 1 day, older than 2 days, older than 8 days, etc)

basically you would use DateCompare function in CF to compare DateAdd to that section's cut-off point. some pseudocode:

today

while dateadd < tomorrow print record

yesterday

while dateadd < 1 week ago print record

earlier this week

while dateadd < 1 month ago print record

Answer 2

you'd have to start on the SQL Side, either a Store Procedure or a User Defined function to add the Today/Yesterday/etc label to each record.

Perhaps:

Select a, b, myFunction(DateColumn) as GroupLabel 
From myTable Order By GroupLabel

Where myFunction takes the date value and returns Today/Yesterday/etc

Answer 3

Query of Queries? - http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_3.html#1157970

<h1>Today:</h1>
<cfquery name="todayRecords" dbtype="query">
    select *
    from originalQuery
    where DateAdded = #createODBCDate(year(now()), month(now()), day(now()))#
</CFQUERY>
<cfdump var="#todayRecords#">

You may want to use the BETWEEN SQL operator. See: http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_7.html

You may also use <cfqueryparam> tag. See: http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html#1102474

Query of Queries might not be the most efficient, but in my opinion it is the cleanest way to represent your intent and it just works.

Answer 4

I agree with Neil. If it were me I would do it all in a Stored Proc and just let the DB hand CF the final results for output.