how to convert the cast statement and case statement from SQL query to fetch xml

• Question

• 

  

  0

  Sign in to vote

  Hi All;

  Below is my query and which i need to fetch xml in crm
  Does any one help me to convert the cast statement and case statement  from SQL query to fetch xml

  Any help on this is much appreciated
  Thanks

  SELECT     o.new_mainprogramme_id AS 'MainProgramme',o.opportunityid,o.new_entrypoint,
  o.statuscode AS 'OppStatus' ,o.parentcontactid,o.new_subprogramme_id AS 'SubProgramme' ,o.ownerid  AS 'BusinessAdvisor',
  o.statuscode AS 'ApplicantStatus' ,
   ct.new_totalhours AS 'TotalHours',
  CAST(ct.new_totalhours / 60 AS VARCHAR(10)) + ':' + RIGHT('0' + CAST(ct.new_totalhours % 60 AS VARCHAR(2)), 2) AS 'TotalHours1',
   (CASE WHEN ct.new_totalhours BETWEEN 0 AND 240 THEN '0 To 4 hrs ' WHEN ct.new_totalhours BETWEEN 241 AND 
                        480 THEN '5 To 8 hrs' WHEN ct.new_totalhours BETWEEN 481 AND 720 THEN '9 To 12 hrs' WHEN ct.new_totalhours BETWEEN 721 AND 
                        960 THEN '13 To 16 hrs' WHEN ct.new_totalhours BETWEEN 961 AND 1200 THEN '17 To 20 hrs' WHEN ct.new_totalhours > 1201 THEN 'More than 20 hrs' END) 
                        AS GroupNoOfHours
  
  
  
  
  
  FROM         opportunity o
   LEFT OUTER JOIN contact AS ct ON ct.contactid = o.parentcontactid 
  
  
  group by 
  
  o.new_mainprogramme_id ,o.opportunityid,o.new_entrypoint,
  o.statuscode ,o.parentcontactid,o.new_subprogramme_id ,o.ownerid,
  o.statuscode,
   ct.new_totalhours
  
  

  
  

  <fetch mapping="logical">
    <entity name="opportunity">
      <attribute name="new_mainprogramme_id" alias="MainProgramme" groupby="true" />
      <attribute name="opportunityid" groupby="true" />
      <attribute name="new_entrypoint" groupby="true" />
      <attribute name="statuscode" alias="OppStatus" groupby="true" />
      <attribute name="parentcontactid" groupby="true" />
      <attribute name="new_subprogramme_id" alias="SubProgramme" groupby="true" />
      <attribute name="ownerid" alias="BusinessAdvisor" groupby="true" />
      <attribute name="statuscode" alias="ApplicantStatus" />
      <link-entity name="contact" to="parentcontactid" from="contactid" alias="ct" link-type="outer">
        <attribute name="new_totalhours" alias="TotalHours1" groupby="true" />
      </link-entity>
    </entity>
  </fetch>

  
  

  ---

  Pradnya07

  Wednesday, April 9, 2014 9:10 AM