How to convert this SQL query to Fetch XML?

Question

Is it possible to write this SQL query as a Fetch XML query? http://www.sql2fetchxml.com fails to convert this query.

select a.ownerid, b.call_count
from opportunity a
join (
select vsg_loan, count(*) as 'call_count'
from phonecall
group by vsg_loan
having count(*) = 2) b
on a.opportunityid = b.vsg_loan
order by b.call_count

I expect the result to be something like this...

Answer 1

Dataverse now has a TDS endpoint, so you can just use that query directly.

Answer 2

There should be something working like this , I hope this works for you mate :)

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="opportunity" alias="a">
        <attribute name="ownerid" />
        <link-entity name="phonecall" from="vsg_loan" to="opportunityid" alias="b" link-type="inner">
          <attribute name="vsg_loan" aggregate="countcolumn" alias="call_count" />
          <filter>
            <condition attribute="statecode" operator="eq" value="0" /> <!-- Optional: Filter based on phone call state if needed -->
          </filter>
          <attribute name="vsg_loan" groupby="true" />
          <filter type="and">
            <condition attribute="vsg_loan" operator="not-null" /> <!-- Optional: Exclude records where vsg_loan is null -->
          </filter>
          <having>
            <condition attribute="vsg_loan" operator="eq" value="2" /> <!-- Adjust the count value as needed -->
          </having>
        </link-entity>
      </entity>
    </fetch>