I have an entity in Dynamics CRM with attributes that look like this (just a small snippet):
Date (datetime type) UserID (GUID type) Operation Entity
2020-09-24 00:47:08.000 16742A71-ED5F-E611-80EA-005056B53B31 Delete Account
2020-09-24 00:47:08.000 16742A71-ED5F-E611-80EA-005056B53B31 Create Opportunity
2020-10-07 05:37:54.000 16742A71-ED5F-E611-80EA-005056B53B31 Update Contact
2020-10-07 02:34:45.000 16742A71-ED5F-E611-80EA-005056B53B31 Update Contact
2020-10-07 09:39:02.000 16742A71-ED5F-E611-80EA-005056B53B31 Update Contact
What I'm looking to do is to get unique combination of Date (don't care about the time portion), UserID, Operation and Entity. So based on the data snipped above, I want to get 3 records as a result of a FetchXML query since the last 3 records were created on the same exact date by the same exact user doing the same exact operation against the same exact entity and the top 2 were unique.
Date (datetime type) UserID (GUID type) Operation Entity
2020-09-24 16742A71-ED5F-E611-80EA-005056B53B31 Delete Account
2020-09-24 16742A71-ED5F-E611-80EA-005056B53B31 Create Opportunity
2020-10-07 16742A71-ED5F-E611-80EA-005056B53B31 Update Contact
This would be an equivalent of GROUP BY cast(createdon as date). Is this possible to achieve in FetchXML at all?
TIA, -Tony.
Yes, this is possible using a dategrouping.
The below example groups by year, but this article explains the other options, including day and month.
<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='opportunity'>
<attribute name='opportunityid' alias='opportunity_count' aggregate='count'/>
<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/>
<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/>
<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='Won' />
</filter>
</entity>