Monday, April 6, 2015

Fetch XML Condition to SQL WHERE

Advanced Find Operator
Fetch XML Operator
FetchXML Filter
SQL Where condition
Equal
eq
<condition attribute="firstname"
value="John" operator="eq"/>
WHERE firstname = 'John'
Not Equal
ne
<condition attribute="firstname"
value="John" operator="ne"/>
WHERE firstname != 'John'
Contains
like
<condition attribute="firstname"
value="John" operator="like"/>
WHERE firstname LIKE '%John%'
Does Not Contains
not-like
<condition attribute="firstname"
value="John" operator="not like"/>
WHERE firstname NOT LIKE '%John%'
Begins with
like
<condition attribute="firstname"
value="John%" operator="like"/>
WHERE firstname LIKE 'John%'
Does Not Begins with
not-like
<condition attribute="firstname"
value="John%" operator="not like"/>
WHERE firstname NOT LIKE 'John%'
Ends with
like
<condition attribute="firstname"
 value="%John" operator="like"/>
WHERE firstname LIKE '%John'
Does Not End with
not-like
<condition attribute="firstname"
value="%John" operator="not like"/>
WHERE firstname NOT LIKE '%John'
Contains Data
not-null
<condition attribute="firstname"
operator="not-null"/>
WHERE firstname IS NOT NULL
Does Not Contains Data
null
<condition attribute="firstname" operator="null"/>
WHERE firstname IS NULL
Is Greater than
gt
<condition attribute="annualincome"
value="123" operator="gt"/>
WHERE AnnualIncome >123
Is Greater than or Equal To
ge
<condition attribute="annualincome"
 value="123" operator="ge"/>
WHERE AnnualIncome >=123
Is Less than
lt
<condition attribute="annualincome"
value="123" operator="lt"/>
WHERE AnnualIncome <123 span="">
Is less than or Equal To
le
<condition attribute="annualincome"
value="123" operator="le"/>
WHERE AnnualIncome <=123
Equals Current User
eq-userid
<condition attribute="createdby"
operator="eq-userid"/>
WHERE CreatedBy ='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
Does Not Equal Current user
ne-userid
<condition attribute="createdby"
operator="ne-userid"/>
WHERE CreatedBy !='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
Equals Current User' Teams
eq-userteams
<condition attribute="ownerid"
operator="eq-userteams"/>
WHERE OwnerId IN ('7BC449A8-5A8F-E311-A63C-0050568C6D7D')
Equals Current user Or User's Teams
eq-useroruserteams
<condition attribute="ownerid"
operator="eq-useroruserteams"/>
WHERE OwnerId IN('7BC449A8-5A8F-E311-A63C-0050568C6D7D',' 09C5427D-88B7-E411-8BFB-0050568C6D7D')
On
on
<condition attribute="createdon"
value="2015-04-01" operator="on"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
On or After
on-or-after
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-after"/>
WHERE CreatedOn >= '2015-04-01 05:00:00'
On or Before
on-or-before
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-before"/>
WHERE CreatedOn < '2015-04-02 05:00:00' 
Yesterday
yesterday
<condition attribute="createdon" operator="yesterday"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
Today
today
<condition attribute="createdon" operator="today"/>
WHERE CreatedOn >= '2015-04-02 05:00:00' AND CreatedOn < '2015-04-03 05:00:00'
Tomorrow
tomorrow
<condition attribute="createdon" operator="tomorrow"/>
WHERE CreatedOn >= '2015-04-03 05:00:00' AND CreatedOn < '2015-04-04 05:00:00' 
Next 7 Days
next-seven-days
<condition attribute="createdon" operator="next-seven-days"/>
WHERE CreatedOn >= '2015-04-02 21:25:41.110' AND CreatedOn < '2015-04-10 05:00:00'
Last 7 Days
last-seven-days
<condition attribute="createdon" operator="last-seven-days"/>
WHERE CreatedOn >= '2015-03-26 05:00:00' AND CreatedOn <= '2015-04-02 21:25:41.110'
Next Week
next-week
<condition attribute="createdon" operator="next-week"/>
WHERE CreatedOn >= '2015-04-05 05:00:00' AND CreatedOn < '2015-04-12 05:00:00' 
Last Week
last-week
<condition attribute="createdon" operator="last-week"/>
WHERE CreatedOn >= '2015-03-22 05:00:00' AND CreatedOn < '2015-03-29 05:00:00'
This Week
this-week
<condition attribute="createdon" operator="this-week"/>
WHERE CreatedOn >= '2015-03-29 05:00:00' AND CreatedOn < '2015-04-05 05:00:00'
Next Month
next-month
<condition attribute="createdon" operator="next-month"/>
WHERE CreatedOn >= '2015-05-01 05:00:00' AND CreatedOn < '2015-06-01 05:00:00' 
Last Month
last-month
<condition attribute="createdon" operator="last-month"/>
WHERE CreatedOn >= '2015-03-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00' 
This Month
this-month
<condition attribute="createdon" operator="this-month"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-05-01 05:00:00' 
Next Year
next-year
<condition attribute="createdon" operator="next-year"/>
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00' 
Last Year
last-year
<condition attribute="createdon" operator="last-year"/>
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
This Year
this-year
<condition attribute="createdon" operator="this-year"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00' 
Last X Hours
last-x-hours
<condition attribute="createdon"
value="1" operator="last-x-hours"/>
WHERE CreatedOn >= '2015-04-02 20:00:00' AND CreatedOn <= '2015-04-02 21:53:49.933'
Next X Hours
next-x-hours
<condition attribute="createdon"
value="1" operator="next-x-hours"/>
WHERE CreatedOn >= '2015-04-02 21:53:49.933' AND CreatedOn < '2015-04-02 23:00:00' 
Last X Days
last-x-days
<condition attribute="createdon"
 value="1" operator="last-x-days"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn <= '2015-04-02 21:53:49.933'
Next X Days
next-x-days
<condition attribute="createdon"
 value="1" operator="next-x-days"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.257' AND CreatedOn < '2015-04-05 05:00:00'
Last X Weeks
last-x-weeks
<condition attribute="createdon"
value="1" operator="last-x-weeks"/>
WHERE CreatedOn >= '2015-03-27 05:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
Next X Weeks
next-x-weeks
<condition attribute="createdon"
value="1" operator="next-x-weeks"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn < '2015-04-11 05:00:00' 
last X Months
last-x-months
<condition attribute="createdon"
 value="1" operator="last-x-months"/>
WHERE CreatedOn >= '2015-03-03 06:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
Next X months
next-x-months
<condition attribute="createdon"
value="1" operator="next-x-months"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn < '2015-05-04 05:00:00' 
Last X Years
last-x-years
<condition attribute="createdon"
value="1" operator="last-x-years"/>
WHERE CreatedOn >= '2014-04-03 05:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
Next X Years
next-x-years
<condition attribute="createdon"
value="1" operator="next-x-years"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn < '2016-04-04 05:00:00' 
Any Time
not-null
<condition attribute="createdon"
operator="not-null"/>
WHERE CreatedOn IS NOT NULL
Older than x months
olderthan-x-months
<condition attribute="createdon"
value="1" operator="olderthan-x-months"/>
WHERE CreatedOn < '2015-03-03 06:00:00'
In Fiscal Year
in-fiscal-year
<condition attribute="createdon"
value="2015" operator="in-fiscal-year"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
In Fiscal Period
in-fiscal-period
<condition attribute="createdon"
value="1" operator="in-fiscal-period"/>
WHERE dbo.fn_GetFiscalPeriod('2014-01-01 00:00:00', 4, CreatedOn, '20') = 1
In Fiscal Period and Year
in-fiscal-period-and-year
<condition attribute="createdon=" 
operator="in-fiscal-period-and-year=">
  <value>01</value>
  <value>2015</value>
</condition>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00' 
In or After Fiscal Period
in-or-after-fiscal-period-and-year
<condition attribute="createdon" 
operator="in-or-after-fiscal-period-and-year">
  <value>01</value>
  <value>2015</value>
</condition>
WHERE CreatedOn >= '2015-01-01 06:00:00'
In or Before Fiscal Period
in-or-before-fiscal-period-and-year
<condition attribute="createdon" 
operator="in-or-before-fiscal-period-and-year">
  <value>01</value>
  <value>2015</value>
</condition>
WHERE CreatedOn < '2015-04-01 05:00:00' 
Last Fiscal Year
last-fiscal-year
<condition attribute="createdon" operator="last-fiscal-year"/>
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00' 
This  Fiscal Year
this-fiscal-year
<condition attribute="createdon" operator="this-fiscal-year"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
Next  Fiscal Year
next-fiscal-year
<condition attribute="createdon" operator="next-fiscal-year"/>
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
Last X  Fiscal Year
last-x-fiscal-years
<condition attribute="createdon"
value="1" operator="last-x-fiscal-years"/>
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00' 
Next X  Fiscal Year
next-x-fiscal-years
<condition attribute="createdon"
value="1" operator="next-x-fiscal-years"/>
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00' 
Last Fiscal Period
last-fiscal-period
<condition attribute="createdon" operator="last-fiscal-period"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00' 
This Fiscal Period
this-fiscal-period
<condition attribute="createdon" operator="this-fiscal-period"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-07-01 05:00:00'
Next Fiscal Period
next-fiscal-period
<condition attribute="createdon" operator="next-fiscal-period"/>
WHERE CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00' 
Last X Fiscal Period
last-x-fiscal-periods
<condition attribute="createdon"
value="1" operator="last-x-fiscal-periods"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
Next X Fiscal Period
next-x-fiscal-periods
<condition attribute="createdon"
value="1" operator="next-x-fiscal-periods"/>
WHERE CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00'


4 comments:

  1. Socialism is a philosophy of failure, the creed of ignorance, and the gospel of envy, its inherent virtue is the equal sharing of misery. See the link below for more info.


    #equal
    www.ufgop.org

    ReplyDelete
  2. Really Great post and written with good approach.Very much appreciated :)

    ReplyDelete
  3. Great article! Would you have details on how to write fetchxml for comparing values between to related entities? For example payment terms on account, and payment terms on invoice to ensure they match.

    ReplyDelete

  4. The CRM system collates all your data from ERP for tour operators , call center or reservation systems, right from the very first sales pitch through to targeted marketing activities this maximizes transparency.All of your customer data is stored centrally in your CRM system which means it can be used for targeted marketing activities. Filter your contacts according to criteria, for example, previous holiday destinations or available budget: this helps you to speak to customer in their language and match their requirements exactly.

    ReplyDelete