locked
Convert SQl to fetch XML RRS feed

  • Question

  • Hi All;

    How can i convert below query to fetch xml

    SELECT DISTINCT format(c.crmi_ebstartdate, 'dd-MM-yyyy') AS StartDate, c.crmi_startdatestring, r.crmi_ebevent, c.crmi_ebstartdate
    FROM         crmi_ebbarcode AS r LEFT OUTER JOIN
                          crmi_ebevent AS c ON r.crmi_ebevent = c.crmi_ebeventid
    WHERE     (c.crmi_ebstartdate >= @Search) AND (c.crmi_ebstartdate < @Search + 1)

    Any help much appreciated

    Thanks


    Pradnya07

    Tuesday, April 22, 2014 9:03 AM

All replies

  • Hi,

    try this one: http://www.sql2fetchxml.com/

    Bye

    Al.


    Alessandro Graps

    Tuesday, April 22, 2014 2:28 PM
  • thanks for your reply

    Currently i am using that website

    i am struggling for the where part

    WHERE     (c.crmi_ebstartdate >= @Search) AND (c.crmi_ebstartdate < @Search + 1)

    how do i represent in fetch xml


    Pradnya07

    Tuesday, April 22, 2014 2:29 PM
  • this is a good post to learn:

    http://blog.rahulsharma.in/2011/04/microsoft-dynamics-crm-2011-fetch-xml.html

    but for you

    <filter type="and">
            <condition attribute="enddate" operator="on-or-after" value="2014-04-05" />
            <condition attribute="enddate" operator="on-or-before" value="2014-04-06" />
          </filter>
        </filter>

    you can see all attubutes here:

    http://msdynamicscrmblog.wordpress.com/2013/05/10/fetch-xml-and-conditionexpression-operators-using-c-in-dynamics-crm-2011/


    Alessandro Graps

    Tuesday, April 22, 2014 3:25 PM
  • Hi,

    is it working?:-) Please if it is ok, May you check this one like answer?

    Thanks a lot

    Al.


    Alessandro Graps

    Wednesday, April 23, 2014 6:36 AM
  • Hi All;

    Thanks for all your reply

    Below is my ssrs report , in which whenever i type in search box 08/04/2014  in Event id i get all the dates for the month of April

    but i need to display only 08/04 /2014

    is there a way i can achieve it ?

    this is my xml for event id

    <fetch distinct="true" mapping="logical">
      <entity name="crmi_ebevent">
        <attribute name="crmi_startdatestring" />
        <attribute name="crmi_ebstartdate" />

    <filter type ="and">
            <condition attribute="crmi_ebebstatus" operator="in" value="@Status"  />

      <condition attribute="crmi_ebstartdate" operator="this-fiscal-period" value="@Search"  />
     
    </filter>

        <link-entity name="crmi_ebbarcode" to="crmi_ebeventid" from="crmi_ebevent" alias="r" link-type="outer">
          <attribute name="crmi_ebevent" />
        </link-entity>

      </entity>
    </fetch>


    Pradnya07

    Wednesday, April 23, 2014 11:09 AM