title: Compare dates between rows from the same input file based on ID and replicate rows by increment date till the last working date using SQL Server
body: <p>I am trying to duplicate rows by comparing the date of the current row with date of the next row for a user ID and row should be duplicated by incrementing the date where &lt; date of the next row.</p>
  
  <p>The last row of the id should increment till the lastworkingdate. If the lastworkingdate is null, should increment the date till current date.</p>
  
  <p>Input:</p>
  
  <p><a href="https://i.stack.imgur.com/wP1Ru.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wP1Ru.png" alt="enter image description here"></a></p>
  
  <p>Output expected</p>
  
  <p><a href="https://i.stack.imgur.com/zCZeT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zCZeT.png" alt="enter image description here"></a></p>
  
  <p>Please suggest if we can implement this logic using SQL Server.</p>
  
  <p>I have tried the below code</p>
  
  <pre><code>WITH cte (User_ID, Start_DateMonth, Start_DateDAY, Last_working_date_text, lead_start_datemonth) AS
      (SELECT User_ID,
              CONVERT(date, CAST(Start_DateMonth AS varchar(50)) + '01') AS Start_DateMonth,
              Start_DateDAY,
              Last_working_date_text,
              LEAD(CONVERT(datetime, CAST(Start_DateMonth AS varchar(MAX)) + '01')) OVER (PARTITION BY User_ID
                                                                                          ORDER BY CONVERT(date, CAST(Start_DateMonth AS varchar(50)) + '01')) AS lead_start_datemonth
       FROM [dbo].[Historic_Headcount3] --mytable
  
       UNION ALL
       SELECT User_ID,
              CONVERT(date, DATEADD(MONTH, 1, ISNULL(Start_DateMonth, GETDATE()))),
              Start_DateDAY,
              Last_working_date_text,
              CONVERT(datetime, CAST(lead_start_datemonth AS varchar(MAX)) + '01') AS lead_start_datemonth
       FROM cte
       WHERE DATEADD(MONTH, 1, Start_DateMonth) &lt; ISNULL(lead_start_datemonth,
                                                         CASE
                                                              WHEN ISDATE(Last_working_date_text) = 1
                                                               AND Last_working_date_text != '#' THEN CONVERT(date, Last_working_date_text)
                                                              ELSE GETDATE()
                                                         END))
  SELECT User_ID,
         Start_DateMonth,
         Start_DateDAY,
         Last_working_date_text
  FROM cte
  ORDER BY User_ID,
           Start_DateMonth;
  </code></pre>
  
  <p>I am getting error</p>
  
  <blockquote>
    <p>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.</p>
  </blockquote>