Sharepoint date manipulation and filtering

I'm having a real problem trying to filter a sharepoint list by date. I want to produce a list of months/years where for each entry there is a count from the list, where the counts include dates that are greater than or equal to the 15th of that month, or less than the 15th of the following month. I can do this fine for any month except for December, where I want the count to go from 15 Dec 2009 to 14 Jan 2010 (for example). I really cannot see why there should be any difference as I am using the same method. If anyone can see a mistake in my code, or a better way of doing it, I would be extremely grateful.

<xsl:template name="generateTable">
      <xsl:param name="numMonths" />
        <xsl:param name="Rows" />
        <xsl:param name="dvt_Rows" />
        <xsl:param name="tday" select="$startDay" />
        <xsl:param name="tmonth" select="($startMonth + msxsl:node-set($numMonths)-1) mod 12 + 1" />
        <xsl:param name="tyear" select="$startYear + floor(($numMonths+msxsl:node-set($startMonth)-1) div 12)" />
        <xsl:variable name="date" select="concat($tday,'/',$tmonth,'/',$tyear)"/>

        <tr>
        <td>
          <xsl:value-of select="ddwrt:FormatDateTime(string($date),3081,'MMMM yyyy')"/>
        </td>
        <td>
        <xsl:choose>
        <xsl:when test="$tmo开发者_运维知识库nth=12">
        <xsl:value-of select="count(/dsQueryResponse/Rows[1]/Row[(
                                    (ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'M yyyy' ) )=(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) ) 
                                    and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) &gt;=$startDay)])+
                                    count(/dsQueryResponse/Rows[1]/Row[(
                concat(string(number(substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'M yyyy' ),1,2))+11),' ',
                string(number(substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'MM yyyy'),4))-1))=string(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) ) 
                and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) &lt;$startDay)])"/>      
        </xsl:when>
        <xsl:otherwise>
        <xsl:value-of select="count(/dsQueryResponse/Rows[1]/Row[(
                                    (ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'M yyyy' ) )=(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) ) 
                                    and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) &gt;=$startDay)])+
                                    count(/dsQueryResponse/Rows[1]/Row[(
                concat(string(number(substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'MM yyyy' ),1,2))-1),
                substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'MM yyyy'),3,6))=(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) ) 
                and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) &lt;$startDay)])"/>
        </xsl:otherwise>
        </xsl:choose>
        </td>
        </tr>

        <xsl:if test="msxsl:node-set($numMonths) &gt; 0">
          <xsl:call-template name="generateTable">
          <xsl:with-param name="numMonths" select="msxsl:node-set($numMonths)-1" />
            <xsl:with-param name="dvt_Rows"/>   
          <xsl:with-param name="Rows"/> 
      </xsl:call-template>
      </xsl:if>


</xsl:template>

In the code, tday, tmonth and tyear are todays date in d/m/y, numMonths is a variable to hold the number of months required in the table, and startDay is 15.

As far as I can see, the adding of 11 months to the Date fields works, but extracting the year, changing it to a number and subtracting 1, is where the problem seems to be. But I can't get it to work, so I have completely run out of simple ideas.

The XML document is the Sharepoint list and I don't know how to share it as pure XML. The only reference I make to the XML is /dsQueryResponse/Rows[1]/Row which selects rows in the Sharepoint list, and uses the Date field (@Date) to perform the filter. Does this help at all?

I'm using Sharepoint 2007, and I started by using the Sharepoint Designer to obtain the Sharepoint list by inserting a dataFormWebPart. It's then possible to access the Sharepoint list in the XSLT by using xpath, /dsQueryResponse/Rows/Row which references all rows in the list. (I have added [1] in my code here to reference a specific list as I have two list data sources).

I would prefer to filter the list in Sharepoint first, and considered that first, but maybe I am doing something wrong, as I can't get that to produce exactly what I want - which includes zeros when there are no entries for a specific month, as well as the >=15 of the month filter. My method almost works which is why it is so frustrating, but I would welcome any alternatives especially if they are simpler and/or quicker. Thanks

As example, this stylesheet:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:key name="kDateByYearAndMonth" match="date"
             use="translate(substring(.,1,7),'-','') -
                  (15 > substring(.,9,2)) * (1 + (substring(.,6,2)='01') * 88)"/>
    <xsl:template match="root">
        <result>
            <xsl:apply-templates select="date[count(. |
                                                key('kDateByYearAndMonth',
                                                    translate(substring(.,1,7),'-','') -
                                                    (15 > substring(.,9,2)) *
                                                    (1 + (substring(.,6,2)='01') * 88)
                                                   )[1]
                                               ) = 1]"/>
        </result>
    </xsl:template>
    <xsl:template match="date">
        <xsl:variable name="vKey" select="translate(substring(.,1,7),'-','') -
                  (15 > substring(.,9,2)) * (1 + (substring(.,6,2)='01') * 88)"/>
        <xsl:variable name="vDates" select="key('kDateByYearAndMonth',$vKey)"/>
        <group year="{substring($vKey,1,4)}" month="{substring($vKey,5,2)}"
               count="{count($vDates)}">
            <xsl:copy-of select="$vDates"/>
        </group>
    </xsl:template>
</xsl:stylesheet>

With this input:

<root>
<date>2001-01-01T00:15:00</date>
<date>2001-01-02T00:15:00</date>
<date>2001-02-03T00:15:00</date>
<date>2001-02-04T00:15:00</date>
<date>2002-03-05T00:15:00</date>
<date>2002-03-06T00:15:00</date>
<date>2002-04-07T00:15:00</date>
<date>2002-04-08T00:15:00</date>
<date>2003-05-09T00:15:00</date>
<date>2003-05-10T00:15:00</date>
<date>2003-06-11T00:15:00</date>
<date>2003-06-12T00:15:00</date>
<date>2004-07-13T00:15:00</date>
<date>2004-07-14T00:15:00</date>
<date>2004-08-15T00:15:00</date>
<date>2004-08-16T00:15:00</date>
<date>2005-09-17T00:15:00</date>
<date>2005-09-18T00:15:00</date>
<date>2005-10-19T00:15:00</date>
<date>2005-10-20T00:15:00</date>
<date>2006-11-21T00:15:00</date>
<date>2006-11-22T00:15:00</date>
<date>2006-12-23T00:15:00</date>
<date>2006-12-24T00:15:00</date>
<date>2007-01-25T00:15:00</date>
<date>2007-01-26T00:15:00</date>
<date>2007-02-27T00:15:00</date>
<date>2007-02-28T00:15:00</date>
<date>2008-03-29T00:15:00</date>
<date>2008-03-30T00:15:00</date>
<date>2008-04-31T00:15:00</date>
</root>

Output:

<result>
    <group year="2000" month="12" count="2">
        <date>2001-01-01T00:15:00</date>
        <date>2001-01-02T00:15:00</date>
    </group>
    <group year="2001" month="1" count="2">
        <date>2001-02-03T00:15:00</date>
        <date>2001-02-04T00:15:00</date>
    </group>
    <group year="2002" month="2" count="2">
        <date>2002-03-05T00:15:00</date>
        <date>2002-03-06T00:15:00</date>
    </group>
    <group year="2002" month="3" count="2">
        <date>2002-04-07T00:15:00</date>
        <date>2002-04-08T00:15:00</date>
    </group>
    <group year="2003" month="4" count="2">
        <date>2003-05-09T00:15:00</date>
        <date>2003-05-10T00:15:00</date>
    </group>
    <group year="2003" month="5" count="2">
        <date>2003-06-11T00:15:00</date>
        <date>2003-06-12T00:15:00</date>
    </group>
    <group year="2004" month="6" count="2">
        <date>2004-07-13T00:15:00</date>
        <date>2004-07-14T00:15:00</date>
    </group>
    <group year="2004" month="8" count="2">
        <date>2004-08-15T00:15:00</date>
        <date>2004-08-16T00:15:00</date>
    </group>
    <group year="2005" month="9" count="2">
        <date>2005-09-17T00:15:00</date>
        <date>2005-09-18T00:15:00</date>
    </group>
    <group year="2005" month="10" count="2">
        <date>2005-10-19T00:15:00</date>
        <date>2005-10-20T00:15:00</date>
    </group>
    <group year="2006" month="11" count="2">
        <date>2006-11-21T00:15:00</date>
        <date>2006-11-22T00:15:00</date>
    </group>
    <group year="2006" month="12" count="2">
        <date>2006-12-23T00:15:00</date>
        <date>2006-12-24T00:15:00</date>
    </group>
    <group year="2007" month="1" count="2">
        <date>2007-01-25T00:15:00</date>
        <date>2007-01-26T00:15:00</date>
    </group>
    <group year="2007" month="2" count="2">
        <date>2007-02-27T00:15:00</date>
        <date>2007-02-28T00:15:00</date>
    </group>
    <group year="2008" month="3" count="2">
        <date>2008-03-29T00:15:00</date>
        <date>2008-03-30T00:15:00</date>
    </group>
    <group year="2008" month="4" count="1">
        <date>2008-04-31T00:15:00</date>
    </group>
</result>

Note: The month' start day is "hardcode", because there can't be variable reference in xsl:key/@use.

Editt: Better key calculation.