CAML Query to Filter On DateTime Field using DateDiff

Question

I'm looking to use javascript and CAML to place a value on a SharePoint page. I need to find the current On-Call person based on the start and end date of a list item, and the next on-call person based on the same criteria. I've done this in PowerApps with a lookup() and datadiff(), but I'm having trouble converting this to CAML.

PowerApps query:

LookUp('PowerAppConnection', And(DateDiff(DateAdd(Now(),-Weekday(Now(),11)),StartDate)>0,DateDiff(DateAdd(Now(),7-Weekday(Now(),11)),StartDate)<=0)).AssignedTo_Text  

Answer 1

Here's how I get this week's on call person - my list has a start date of a monday and a turnover date of Sunday. I added an or statement to get the next week person. This week starts previous to day and ends after today. Next week starts previous to 7 days from now and ends after that date...

<Query><Where>
<Or> 
 <And>
    <Leq><FieldRef Name='Start'  /><Value Type='DateTime'><Today /></Value></Leq>
    <Gt><FieldRef Name='Turn_x002d_Over'  /><Value Type='DateTime'><Today /></Value></Gt>
 </And>
 <And>
    <Leq><FieldRef Name='Start'  /><Value Type='DateTime'><Today OffsetDays='7'/></Value></Leq>
    <Gt><FieldRef Name='Turn_x002d_Over'  /><Value Type='DateTime'><Today OffsetDays='7'/></Value></Gt>
 </And>  
</Or>
 </Where></Query>