CAML Query to return all row ID's where Date expires is less then Date Completed

Question

I need a CAML Query for SP that Compares the Date expires and date Completed and returns all row ID's where the condition is true. I then have a count set up that will count the items in the collection variable and set that to a new variable then the variable is used to update a status page. Please Help been at this for days and cant seem to figure it out. Below is the code I currently have that's not working. The Query below seems to compare the Date expires with the current items date completed I want to look at each row and compare them separately, if date expires is less then date completed return ID. Thank you guys for any assistance.

 <Query>
   <Lists>
     <List ID="{my-list-id}" />
   </Lists>
   <ViewFields>
     <FieldRef Name="ID" />
   </ViewFields>
  <Where>
     <And>
       <And>
         <Eq>
           <FieldRef Name="Status"  />
           <Value Type="Choice">Closed</Value>
        </Eq>
        <Eq>
           <FieldRef Name="twoDigTskYr"  />
           <Value Type="Number">18</Value>
         </Eq>
       </And>
       <Gt>
         <FieldRef Name="DateExpires"  />
         <Value Type="DateTime">{ItemProperty:Date_x0020_Completed}</Value>
       </Gt>
     </And>
    </Where>
   <ViewAttributes Scope="Recursive" />
  </Query>

Niels V Niels V · Accepted Answer · 2018-02-02T10:23:06

You cannot compare two fields with a CAML query. One workaround is to add a calculated field to your list which does the comparison, because you can filter on a calculated field in CAML.

CAML Query to return all row ID's where Date expires is less then Date Completed

1 Answers