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'


Posted by at 4 comments:
Labels: , , ,
Subscribe to: Posts (Atom)