I've tried this a few different ways, trying to find someone who donated "Last Year", but not "This Year".
Not that it matters, but dates used for last year= 7/1/2020 to 6/30/2021, and date used for this year = 7/1/2021 to 6/30/2022.
Just trying to figure out the best way to do this when there are underlying "conditions" on both sides to the same entity.
Method one (Returns Rows, but includes people that donated in both years, along with those that didn't in some cases, not all, weird):
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="contact">
<attribute name="contactid" />
<attribute name="fullname" />
<attribute name="emailaddress1" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition entityname="aa" attribute="altai_fr_donationid" operator="not-null" />
<condition entityname="bb" attribute="altai_fr_donationid" operator="null" />
</filter>
<link-entity name="altai_fr_donation" from="altai_contactid" to="contactid" link-type="outer" alias="aa">
<attribute name="altai_fr_donationid" />
<attribute name="altai_collecteddate" />
<attribute name="altai_collectedamount" />
<filter type="and">
<condition attribute="altai_collecteddate" operator="on-or-after" value="@lfystartdate" />
<condition attribute="altai_collecteddate" operator="on-or-before" value="@lfyenddate" />
</filter>
</link-entity>
<link-entity name="altai_fr_donation" from="altai_contactid" to="contactid" link-type="outer" alias="bb">
<attribute name="altai_fr_donationid" />
<filter type="and">
<condition attribute="altai_collecteddate" operator="on-or-after" value="@tfystartdate" />
<condition attribute="altai_collecteddate" operator="on-or-before" value="@tfyenddate" />
</filter>
</link-entity>
</entity>
</fetch>
Method Two (Returns 0 rows always):
<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
<entity name='contact' >
<attribute name='contactid' />
<attribute name='fullname' />
<attribute name='emailaddress1' />
<filter type='and' >
<!-- Contact In Active State -->
<condition attribute='statecode' operator='eq' value='0' />
<!-- LAST FISCAL YEAR - Donation in Active State, Donation EXISTS, On or after start selected, on or before end selected -->
<condition entityname='lfy' attribute='statecode' operator='eq' value='0' />
<condition entityname='lfy' attribute='altai_fr_donationid' operator='not-null' />
<condition entityname='lfy' attribute='altai_collecteddate' operator='on-or-after' value='@lfystartdate' />
<condition entityname='lfy' attribute='altai_collecteddate' operator='on-or-before' value='@lfyenddate' />
<!-- THIS FISCAL YEAR - Donation in Active State, Donation DOES NOT EXIST, On or after start selected, on or before end selected -->
<condition entityname='tfy' attribute='statecode' operator='eq' value='0'/>
<condition entityname='tfy' attribute='altai_fr_donationid' operator='null' />
<condition entityname='tfy' attribute='altai_collecteddate' operator='on-or-after' value='@tfystartdate' />
<condition entityname='tfy' attribute='altai_collecteddate' operator='on-or-before' value='@tfyenddate' />
</filter>
<link-entity name='altai_fr_donation' from='altai_contactid' to='contactid' link-type='outer' alias='lfy'>
</link-entity>
<link-entity name='altai_fr_donation' from='altai_contactid' to='contactid' link-type='outer' alias='tfy'>
</link-entity>
</entity>
</fetch>
