Caml Query weekly and monthly

Question

I want to build a CAML query that show the data on monthly and weekly basis. Please help me to provide the best solutions for the query

In SharePoint there is a list created for leave application that is having five column Name,type of leave , details , start date and end date . I want to fetch the data from the list that will show weekly employees onleave and monthly employees on leave

above is my list image

Now from this i have the html page to display the data from above list Now please provide me the query that i add in my javscript on where condition is check for month and week to display in my html page using script

Answer 1

OK it take me a while, but here is the solution: You need to create 2 calculated columns for Month filtering and 2 calculated columns for Week filtering. View with filtering for each.

EndDate in the formulas is the EndDate internal name of the our colum "end date". Replace ; with , depends on environment localization.

Month Filtering

Create Calculated columns:

• StartMonth - formula: =DATE(YEAR(EndDate);MONTH(EndDate);1) outcome: DateTime, Date only
• EndMonth - formula =DATE(YEAR(EndDate);MONTH(EndDate)+1;1)-1 outcome: DateTime, Date only

Create view with filter:

MonthLeaving - filter:

Week filtering

Create Calculated columns:

• StartOfWeek - formula: =EndDate-WEEKDAY(EndDate)+1 outcome: DateTime, Date only
• EndOfWeek - formula: =StartOfWeek+6 outcome: DateTime, Date only

Create view with filter:

WeekLeaving - filter:

How it looks like:

It worked well for me. Hope you like it!

CAML Queries instead of SharePoint Views: Replace (Today) with date in format YYYY-MM-dd.

Monthly Leave:

<Query>
   <Where>
      <And>
         <Leq>
            <FieldRef Name='StartMonth' />
            <Value Type='Calculated'>(Today)</Value>
         </Leq>
         <Geq>
            <FieldRef Name='EndMonth' />
            <Value Type='Calculated'>(Today)</Value>
         </Geq>
      </And>
   </Where>
</Query>

Weekly Leave:

<Query>
   <Where>
      <And>
         <Leq>
            <FieldRef Name='StartOfWeek' />
            <Value Type='Calculated'>(Today)</Value>
         </Leq>
         <Geq>
            <FieldRef Name='EndOfWeek' />
            <Value Type='Calculated'>(Today)</Value>
         </Geq>
      </And>
   </Where>
</Query>