Inside FetchXML pt 6 – datetime grouping

Last time I looked at aggregates, but unforgivably I missed out how to apply grouping to date/time attributes:

Not to forget – data grouping….! 😉https://t.co/pLqQo35mc3

— Jonas #LazyDev Rapp ᴹᴠᴾ 🇸🇪 (@rappen) December 18, 2019

Grouping works quite simply for data types such as text, picklist or lookup values, but a simple grouping of date/time values wouldn’t work very usefully. They are stored with second (or in some cases, millisecond) precision, so in most cases no two records will have exactly the same value and each record will end up in its own group.

More typically you’ll want to group records that have a value in the same day or month to generate summary charts or reports, and that’s exactly what FetchXML does for you using the dategrouping attribute:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order alias="occurrences" descending="true" />
  </entity>
</fetch>

This example will get the number of contacts created each month.

Available Groupings

Date/time values can be grouped by:

• day
• week
• month
• quarter
• year
• fiscal period
• fiscal year

If you are grouping by a date/time column, you must use one of these groupings. You can’t group by the raw date/time value – if you try you’ll get the error Invalid value '' for dategrouping.

You’ll notice that there’s no support for anything more precise than a day – you can’t group by hours for example. If you have a need for that you’ll need to retrieve each individual record and implement your own grouping logic.

When you group by day/week/month/quarter, you get the appropriate day/week/month/quarter number. For example, when grouping by month you get a month number 1 – 12. Note that this means records in the same month from different years will be included in the same month. If this isn’t the behaviour you want, e.g. you want to keep January 2019 separate from January 2020, you need to include a separate grouping by year as well:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="createdon" alias="year" groupby="true" dategrouping="year" />
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order alias="occurrences" descending="true" />
  </entity>
</fetch>

For week grouping you get a number 1 – 53, quarter is 1 – 4 and day is 1 – 31.

SQL Equivalents

These date/time grouping options are equivalent to using the DATEPART function in SQL:

SELECT   DATEPART(month, createdon) AS month,
         count(*) AS count
FROM     contact
GROUP BY DATEPART(month, createdon)

There’s no direct SQL equivalent for the fiscal period / year grouping options as these are application specific. However, in SQL 4 CDS I’ve extended the DATEPART function to allow fiscalperiod or fiscalyear to be specified as the part type name parameter.

You’ll need SQL 4 CDS 1.0.5 (which I should be releasing soon) or later to use the DATEPART function.