Date array and Sorting

[TheScarecrow]

I have a date array that i created from a database.  I am trying to sort the dates into the correct order with the upcoming on top.... the best would be that after the date has passed it would go to the bottom of the list. Can anyone tell me if I am on the right path with what I have so far and give me any hints?  thanks (the second loop give me an error)

<!--- Declare query array --->

<cfset dateArray = arraynew(1)>

<!--Poppulate Array row by row--->

<cfloop query="CDE_Dates2">

          <cfset dateArray[currentRow][1] = event_date>

</cfloop>

<!--- Sort Array Dates --->

<cfloop index="i" from="1" to="#arrayLen(dateArray)#">

          <cfset dateVar = DateFormat(dateArray,"YYYY/MM/DD")>

</cfloop>

[Carl Von Stetten]

I think I read that as you want dates sored in an order something like this:  future dates at the top, sorted in ascending order (most imminent first to farthest future last), then past dates at the bottom in ascending order (oldest first to most recent past last).

The easiest way would be to either do this when you query the database (in your CDE_Dates2) or do a query-of-query on CDE_Dates2.  You can do it using a union and adding an extra column for sorting.  Here's some SQL pseudo-code:

SELECT dateColumn, otherColumn, anotherColumn, 0 AS sortOverride

FROM someTable

WHERE dateColumn > <cfqueryparam value="#Now()#" cfsqltype="cf_sql_date">

UNION

SELECT dateColumn, otherColumn, anotherColumn, 1 AS sortOverride

FROM someTable

WHERE dateColumn <= <cfqueryparam value="#Now()#" cfsqltype="cf_sql_date">

ORDER BY sortOverride, dateColumn

This will force the future dates to the top and put past dates at the bottom.

You could do the same thing in a QofQ.

-Carl V.

[TheScarecrow]

I understand how that code works ... I am confused about the sortOverride?  is this a table column or just a temp name?

[Carl Von Stetten]

It's a temp column being added by the query - it won't exist in the table, just when the query is run.

-Carl V.

[TheScarecrow]

What if the year spans 12 and 13 .... can I still make this work?

[Carl Von Stetten]

Can you elaborate a bit?  I'm not sure what you mean.

-Carl V.

[TheScarecrow]

So say my dates are between now and a year ago... so I want the dates to appear as such:

September 29,2013

October 26, 2013

December 14, 2013

December 14, 2013

October 20, 2012

December 1, 2012

January 26, 2013

March 2, 2013

[Carl Von Stetten]

Yeah, that pseudo code should work fine as is.

-Carl V.

[TheScarecrow]

Ok I have thise query....

<CFQUERY name="dates" datasource="xxx">

SELECT dateColumn, otherColumn, anotherColumn, 0 AS sortOverride

FROM someTable

WHERE dateColumn > <cfqueryparam value="#Now()#" cfsqltype="cf_sql_date">

</CFQUERY>

I only get

10/26/2013

12/14/2013

12/14/2013

12/14/2013

There are like 17 dates the go back a year.   How do I get those to show up as well.? 

[Carl Von Stetten]

@TheScarecrow,

You're missing half the query logic from the example I gave you.  You need the UNION and the partial query that follows.  The part you used grabs the future dates; the part you left out grabs the past dates.

-Carl V.

[TheScarecrow]

Thanks that did it.  I got it working.