How to group items by date range in XSLT?

Question

I have a bunch of data that looks a little like this:

<item>
  <colour>Red</colour>
  <date_created>2009-10-10 12:01:55</date_created>
  <date_sold>2009-10-20 22:32:12</date_sold>
</item>
<item>
  <colour>Blue</colour>
  <date_created>2009-11-01 13:21:00</date_created>
  <date_sold>2009-11-21 12:32:12</date_sold>
</item>
<item>
  <colour>Blue</colour>
  <date_created>2009-10-29 21:23:02</date_created>
  <date_sold>2009-10-20 02:02:22</date_sold>
</item>
<item>
  <colour>Red</colour>
  <date_created>2009-11-02 09:11:51</date_created>
  <date_sold>2009-11-20 09:15:53</date_sold>
</item>
<item>
  <colour>Red</colour>
  <date_created>2009-10-18 11:00:55</date_created>
  <date_sold>2009-10-20 11:12:22</date_sold>
</item>

Now what I would like to be able to do is to run that through an XSLT stylesheet such that I get ouput looking like this:

Colour | In stock 1 week | In stock 2 weeks | In stock 3 weeks
Red    |       1         |        3         |       2
Blue   |       0         |        2         |       1

Currently I have a stylesheet that uses basic muenchian grouping to show that 30% of stock was Red and 70% blue, but I can't see a way to find the number of nodes withing a given date range.

Is there a way to use keys to select a range? Do I need to create some kind of intermediate data node? Is there a different route that shows I'm barking up the wrong tree with both those suggestions? Is this even possible with XSLT or do I need to find a way to change the data source?

Answer 1

If you can use EXSLT date and time functions, you could use something like the following to get items by time in stock (in weeks, rounded up):

<xsl:key 
  name="items-by-weeks-in-stock" 
  match="//item"
  use="ceiling(date:seconds(date:difference(translate(date_created, ' ', 'T'),
                                            translate(date_sold, ' ', 'T')))
               div 604800)"/>