title: How to break up a date range in sql by months
body: <p>I have two sql tables. they are for employees at a company. one table has the usual details (id,name,surname) and another table has their leave info (empid *foreign key to id in employee table,datefrom,dateto)</p>
  
  <p>I needed to break down the leave structure so that we can display the dates. in an output, we would have :
  DateFrom       DateT
  18-01-2013     19-01-2013</p>
  
  <p>now if the employee has taken leave that goes over the month (ie, it goes from the 29 to the 5), I would print the values like so:
  DateFrom       DateT
  29-01-2013     31-01-2013
  01-02-2013     05-02-2013</p>
  
  <p>to print this, I used <a href="https://stackoverflow.com/questions/6269889/breaking-up-a-date-range-by-month">breaking up a date range by month</a>
  which helped break it down, thanks for that answer.
  but how do I display which employee is getting all that leave? as I said, the tables are linked. I tried using </p>
  
  <pre><code>select
      e.Firstname,
      e.Surname,
      e.MobileNum,
      convert(varchar(11),case when DateFrom &gt; MonthStart then DateFrom else MonthStart end) as BeginDate,
      convert(varchar(11),case when DateTo &lt; MonthEnd then DateTo else MonthEnd end) as EndDate
  
  from(
      select l.*,
      (
          dateadd(month,datediff(month,0,l.datefrom)+v.number,0)
      ) as MonthStart,
      DATEADD(day,-1,
          dateadd(month,datediff(month,0,l.datefrom)+v.number+1,0)
      ) as MonthEnd
  
  from EmployeeLeave l, Employees E
       inner join master..spt_values v on v.type='P' 
       and v.number between 0 and DATEDIFF(month,l.datefrom,l.dateto)
  ) s 
  </code></pre>
  
  <p>but I get errors saying, multipart identifier could not be bound. and I can't find a spot to say </p>
  
  <pre><code>where l.empid = e.empid
  </code></pre>
  
  <p>so could someone please tell me how to fix this. would like to see the employee that is taking leave off </p>