Summation of calculated date difference?

DavrosSpignot · October 5th, 2007, 08:37 AM

I am relatively new to xsl but I get some of the basics. I am stumped however by how to go about this one:

here is my XML (i've chopped bit out for clarity so if it badly formatted, assume human error)

<root>
  <parameters/>
  <tb_Clients>
    <tb_ClientsItem>
      <ClientId>37</ClientId>
      <tb_ObjectTag>
        <tb_ObjectTagItem>
          <refId>37</refId>
          <tagId>16</tagId>
          <tb_GPS>
            <tb_GPSItem>
              <GPSId>29</GPSId>
              <tagId>16</tagId>
              <captured_dt>05/09/2007 02:25:00</captured_dt>
              <locationId>13</locationId>
            </tb_GPSItem>
            <tb_GPSItem>
              <GPSId>30</GPSId>
              <tagId>16</tagId>
              <captured_dt>05/09/2007 02:30:00</captured_dt>
              <locationId>13</locationId>
            </tb_GPSItem>
            <tb_GPSItem>
              <GPSId>32</GPSId>
              <tagId>16</tagId>
              <captured_dt>05/09/2007 02:40:00</captured_dt>
              <locationId>15</locationId>
            </tb_GPSItem>
            <tb_GPSItem>
              <GPSId>34</GPSId>
              <tagId>16</tagId>
              <captured_dt>05/09/2007 02:50:00</captured_dt>
              <locationId>2</locationId>
            </tb_GPSItem>
            <tb_GPSItem>
              <GPSId>36</GPSId>
              <tagId>16</tagId>
              <captured_dt>05/09/2007 03:00:00</captured_dt>
              <locationId>15</locationId>
            </tb_GPSItem>
          </tb_GPS>
        </tb_ObjectTagItem>
        <tb_ObjectTagItem>
          <objectTagId>2</objectTagId>
          <refId>37</refId>
          <tagId>18</tagId>
          <tb_GPS>
            <tb_GPSItem>
              <GPSId>18</GPSId>
              <tagId>18</tagId>
              <captured_dt>09/05/2007 01:30:00</captured_dt>
              <locationId>1</locationId>
            </tb_GPSItem>
            <tb_GPSItem>
              <GPSId>23</GPSId>
              <tagId>18</tagId>
              <captured_dt>09/05/2006 01:55:00</captured_dt>
              <locationId>13</locationId>
            </tb_GPSItem>
          </tb_GPS>
        </tb_ObjectTagItem>
      </tb_ObjectTag>
    </tb_ClientsItem>
  </tb_Clients>
</root>

basically each client has one or more GPS tags linked to them and we record periodically a location for these tags. This is about the average wait time between being in one location to the next. Specifically from location 1 to location 15

So I used a couple of nested foreach loops to get the earliest time (actually used minumum GPSID as equivilent, numerical easier than dates?) to select the first locationid = 1, then a nested loop to find the earliest locationId = 15 within that. I can set the $waittime variable within scope, easy but how to I sum a calculated number? especially given it's a date difference calculation? I understand that XLS is not a procedural language and I need some kind of sum(calculated bit) but it's beyond me. I am ploughing on through Jeni's xslt pages in the mean time.

another piece of mhkay genius please!

here's my current xsl attempt.

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:DateTimeDifference="urn:DateTimeDifference">

<xsl:for-each select="root/tb_Clients/tb_ClientsItem">
  <xsl:for-each select="tb_ObjectTag/tb_ObjectTagItem/tb_GPS/tb_GPSItem[locationId=1]">
    <xsl:sort select="tagLocationId" order="descending"/>
    <xsl:if test="position()=1">
      <xsl:variable name="tagLocationId" select="tagLocationId"/>
      <xsl:variable name="starttime" select="captured_dt"/>
      <xsl:for-each select="../../../../tb_ObjectTag/tb_ObjectTagItem/tb_GPS/tb_GPSItem[locationId=15 and tagLocationId > $tagLocationId]">
        <xsl:sort select="tagLocationId" order="descending"/>
        <xsl:if test="position()=1">
      <xsl:variable name="waittime" select="DateTimeDifference:GetValue($starttime, $endtime, 'M')"/>

        </xsl:if>
      </xsl:for-each>
    </xsl:if>
  </xsl:for-each>
</xsl:for-each>

so in this example it should be picking out (and does) 09/05/2007 01:30:00 (GPSID 18) as $starttime and 05/09/2007 03:00:00 (GPSID 36) as $endtime . So for this client waittime is the difference. Now I need a grand summary across multiple clients???

mhkay · October 5th, 2007, 08:47 AM

You're using processor-specific extension functions here to do date arithmetic, so (a) we'll need to know what processor you are using, and (b) you'll probably need to go to the support site for that processor to get help.

In XSLT 2.0 date/time arithmetic is standardized and you no longer need to use vendor-specific extensions.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

DavrosSpignot · October 5th, 2007, 10:02 AM

all the function does is get the time difference, so any xsl 2.0 function that does that is fine. I only used a "referenced function" (apologies for incorrect terms) because I could not work out how to just do "datediff(a,b)" in 2.0.

mhkay · October 5th, 2007, 10:47 AM

You will first have to convert your dates from the format 05/09/2007 into either 2007-09-05 or 2007-05-09 (depending on whether they are in May or September, I can't tell without further info).

Having done that, you can subtract two xs:dateTime values (using the "-" operator) to get an xs:dayTimeDuration value, and you can sum a sequence of durations using the sum() function, or you can get the average using avg().

I'm having trouble converting your code because I see a tagId and a locationId but no tagLocationId.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

DavrosSpignot · October 7th, 2007, 05:29 PM

I have established that I have xslt 1.0 as I am working on windows with asp.net / c# / visual studio. Since I assume I have msXML 5.0 at best and this only has 1.0. Right?

I have also (I think) worked out that there is little difference in the logic I need to workout, based on whether I use xs:dateTime from 2.0 (which I cannot) or my own function DateTimeDifference (I am hence the vendor of the extension) which just return a Number / Integer for the minutes between the dates. For the purposes of the exercise, we can assume I have 2.0 and I am using xs:dateTime and dayTimeDuration, unless I have really missed a point somewhere.

I have formatted the dates to standard formats as required. Here is the revised XML with multiple Client Nodes. Sorry for the mistake.

DavrosSpignot · October 7th, 2007, 05:30 PM

Revised XML

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <parameters/>
  <tb_Clients>
    <tb_ClientsItem>
      <ClientId>1</ClientId>
      <FirstName>James</FirstName>
      <LastName>Lynes</LastName>
      <DOB/>
      <tb_ObjectTag/>
    </tb_ClientsItem>
    <tb_ClientsItem>
      <ClientId>2</ClientId>
      <FirstName>Neil</FirstName>
      <LastName>Taber</LastName>
      <DOB/>
      <tb_ObjectTag>
        <tb_ObjectTagItem>
          <objectTagId>3</objectTagId>
          <refId>2</refId>
          <tagId>17</tagId>
          <activated>20070829 12:00:00</activated>
          <deactivated>20070830 12:00:00</deactivated>
          <tb_tagList>
            <tb_tagListItem>
              <tagId>17</tagId>
              <tagmac>4C7F1H</tagmac>
            </tb_tagListItem>
          </tb_tagList>
          <tb_tagLocation/>
        </tb_ObjectTagItem>
      </tb_ObjectTag>
    </tb_ClientsItem>
    <tb_ClientsItem>
      <ClientId>3</ClientId>
      <FirstName>Mike</FirstName>
      <LastName>Beckett</LastName>
      <DOB/>
      <tb_ObjectTag>
        <tb_ObjectTagItem>
          <objectTagId>5</objectTagId>
          <refId>3</refId>
          <tagId>6</tagId>
          <activated>20070830 12:00:00</activated>
          <deactivated>20080901 12:00:00</deactivated>
          <tb_tagList/>
          <tb_tagLocation>
            <tb_tagLocationItem>
              <tagLocationId>28</tagLocationId>
              <tagId>6</tagId>
              <captured_dt>20070905 02:20:00</captured_dt>
              <locationId>15</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>15</locationId>
                  <locationLongDesc>location 15</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>31</tagLocationId>
              <tagId>6</tagId>
              <captured_dt>20070905 02:35:00</captured_dt>
              <locationId>1</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>1</locationId>
                  <locationLongDesc>location 1</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>33</tagLocationId>
              <tagId>6</tagId>
              <captured_dt>20070905 02:45:00</captured_dt>
              <locationId>2</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>2</locationId>
                  <locationLongDesc>location 2</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>35</tagLocationId>
              <tagId>6</tagId>
              <captured_dt>20070905 02:55:00</captured_dt>
              <locationId>15</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>15</locationId>
                  <locationLongDesc>location 15</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>37</tagLocationId>
              <tagId>6</tagId>
              <captured_dt>20070905 03:05:00</captured_dt>
              <locationId>15</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>15</locationId>
                  <locationLongDesc>location 15</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
          </tb_tagLocation>
        </tb_ObjectTagItem>
      </tb_ObjectTag>
    </tb_ClientsItem>
    <tb_ClientsItem>
      <ClientId>4</ClientId>
      <FirstName>Dave</FirstName>
      <LastName>Synott</LastName>
      <DOB/>
      <tb_ObjectTag/>
    </tb_ClientsItem>
    <tb_ClientsItem>
      <ClientId>5</ClientId>
      <FirstName>Nobby</FirstName>
      <LastName>Styles</LastName>
      <DOB/>
      <tb_ObjectTag/>
    </tb_ClientsItem>
    <tb_ClientsItem>
      <ClientId>37</ClientId>
      <FirstName>Dave</FirstName>
      <LastName>sxnott</LastName>
      <DOB/>
      <tb_ObjectTag>
        <tb_ObjectTagItem>
          <objectTagId>1</objectTagId>
          <refId>37</refId>
          <tagId>16</tagId>
          <activated>20070828 03:13:00</activated>
          <deactivated/>
          <tb_tagList>
            <tb_tagListItem>
              <tagId>16</tagId>
              <tagmac>4C7F1G</tagmac>
            </tb_tagListItem>
          </tb_tagList>
          <tb_tagLocation>
            <tb_tagLocationItem>
              <tagLocationId>29</tagLocationId>
              <tagId>16</tagId>
              <captured_dt>20070905 02:25:00</captured_dt>
              <locationId>13</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>13</locationId>
                  <locationLongDesc>location 13</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>30</tagLocationId>
              <tagId>16</tagId>
              <captured_dt>20070905 02:30:00</captured_dt>
              <locationId>13</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>13</locationId>
                  <locationLongDesc>location 13</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>32</tagLocationId>
              <tagId>16</tagId>
              <captured_dt>20070905 02:40:00</captured_dt>
              <locationId>15</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>15</locationId>
                  <locationLongDesc>location 15</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>34</tagLocationId>
              <tagId>16</tagId>
              <captured_dt>20070905 02:50:00</captured_dt>
              <locationId>2</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>2</locationId>
                  <locationLongDesc>location 2</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>36</tagLocationId>
              <tagId>16</tagId>
              <captured_dt>20070905 03:00:00</captured_dt>
              <locationId>15</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>15</locationId>
                  <locationLongDesc>location 15</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
          </tb_tagLocation>
        </tb_ObjectTagItem>
        <tb_ObjectTagItem>
          <objectTagId>2</objectTagId>
          <refId>37</refId>
          <tagId>18</tagId>
          <activated>20050828 12:00:00</activated>
          <deactivated>20070828 11:11:11</deactivated>
          <tb_tagList>
            <tb_tagListItem>
              <tagId>18</tagId>
              <tagmac>4C7F1I</tagmac>
            </tb_tagListItem>
          </tb_tagList>
          <tb_tagLocation>
            <tb_tagLocationItem>
              <tagLocationId>18</tagLocationId>
              <tagId>18</tagId>
              <captured_dt>20070509 01:30:00</captured_dt>
              <locationId>1</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>1</locationId>
                  <locationLongDesc>location 1</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>19</tagLocationId>
              <tagId>18</tagId>
              <captured_dt>20070509 01:35:00</captured_dt>
              <locationId>1</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>1</locationId>
                  <locationLongDesc>location 1</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
            <tb_tagLocationItem>
              <tagLocationId>23</tagLocationId>
              <tagId>18</tagId>
              <captured_dt>20060509 01:55:00</captured_dt>
              <locationId>13</locationId>
              <tb_Locations>
                <tb_LocationsItem>
                  <locationId>13</locationId>
                  <locationLongDesc>location 13</locationLongDesc>
                </tb_LocationsItem>
              </tb_Locations>
            </tb_tagLocationItem>
          </tb_tagLocation>
        </tb_ObjectTagItem>
      </tb_ObjectTag>
    </tb_ClientsItem>
  </tb_Clients>
</root>

DavrosSpignot · October 7th, 2007, 05:35 PM

And the xslt file.

as I say, assume that the rfidExtension can be replaced by the xs class to allow access to xs:dateTime etc.

suitable StartDepartment and EndDepartment are 1 and 15

If you can help me come up with the average waittime, I would be very grateful. As well as solving a problem, it will give me more of an insight to solve future problems myself. I just can't come up with the logic.

Also if I am being wasteful in my existing code please let me know. It's the best way my limited knowledge could get where I wanted.

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:rfid="urn:RFIDExtension">
  <xsl:output method="html" omit-xml-declaration="yes"/>
  <xsl:param name="StartDepartment" select="0"/>
  <xsl:param name="EndDepartment" select="0"/>
  <xsl:template match="/">
    <link rel="stylesheet" href="/reports/main.css"/>
    <table border="0" cellspacing="0" cellpadding="3">
      <tr>
        <th style="text-align:center;">Client ID</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th style="text-align:center;">Start Time</th>
        <th style="text-align:center;">End Time</th>
        <th style="text-align: center;">Wait Time</th>
      </tr>
      <xsl:for-each select="root/tb_Clients/tb_ClientsItem">
        <xsl:sort select="LastName"/>
        <xsl:sort select="FirstName"/>
        <xsl:variable name="Clientid" select="ClientId"/>
        <xsl:variable name="lastname" select="LastName"/>
        <xsl:variable name="firstname" select="FirstName"/>
        <xsl:for-each select="tb_ObjectTag/tb_ObjectTagItem/tb_tagLocation/tb_tagLocationItem[locationId=$StartDepartment]">
          <xsl:sort select="tagLocationId" order="descending"/>
          <xsl:if test="position()=1">
            <xsl:variable name="tagLocationId" select="tagLocationId"/>
            <xsl:variable name="starttime" select="captured_dt"/>
            <xsl:for-each select="../../../../tb_ObjectTag/tb_ObjectTagItem/tb_tagLocation/tb_tagLocationItem[locationId=$EndDepartment and tagLocationId > $tagLocationId]">
              <xsl:sort select="tagLocationId" order="descending"/>
              <xsl:if test="position()=1">
                <tr>
                  <td style="width: 70px; text-align:center;">
                    <xsl:value-of select="$Clientid"/>
                  </td>
                  <xsl:variable name="endtime" select="captured_dt"/>
                  <td style="width: 170px; ">
                    <xsl:value-of select="$firstname"/>
                  </td>
                  <td style="width: 170px; ">
                    <xsl:value-of select="$lastname"/>
                  </td>
                  <td style="text-align:center;">
                    <xsl:value-of select="$starttime"/>
                  </td>
                  <td style="text-align:center;">
                    <xsl:value-of select="captured_dt"/>
                  </td>
                  <td style="text-align:center; width:70px;">
                    <xsl:variable name="waittime" select="rfid:DateTimeDifference($starttime, $endtime, 'M')"/>
                    <xsl:value-of select="$waittime"/>
                  </td>
                </tr>
              </xsl:if>
            </xsl:for-each>
          </xsl:if>
        </xsl:for-each>
      </xsl:for-each>
    </table>
    <p>Average Wait Time: XXXX seconds</p>
  </xsl:template>
</xsl:stylesheet>