title: SQL - Convert few dates records to single date range
body: <p>I have 2 table Named Leave Request and Leave Request Details and it contain data like below</p>
  
  <pre><code>Leave Request
  Leave_Req_Id|start_date | end_date  |
  ------------+-----------+-----------+
    lvl10001  | 2013-01-05| 2013-01-08|
              |           |           |
  
  Leave Request Detail
  Req_Detail_Id |start_date | end_date   | canceled | 
  --------------+-----------+------------+----------+-
      lvl10001  | 2013-01-05| 2013-01-05 |   no     | 
      lvl10001  | 2013-01-06| 2013-01-06 |   no     | 
      lvl10001  | 2013-01-07| 2013-01-07 |   yes    |   
      lvl10001  | 2013-01-08| 2013-01-08 |   no     | 
  </code></pre>
  
  <p>The result that I want is to exclude the cancelled record and put in the data into a view table like below</p>
  
  <pre><code>    Report_Id |start_date | end_date   | 
  --------------+-----------+------------+-
      rep10001  | 2013-01-05| 2013-01-06 | 
      rep10002  | 2013-01-08| 2013-01-08 | 
  
  If the cancelled record starts from the front or back of the range, the 
  result should be like below
   Eg. Cancelled on 2013-01-05 only
      Report_Id |start_date | end_date   | 
  --------------+-----------+------------+-
      rep10001  | 2013-01-06| 2013-01-08 | 
                |           |            | 
  
   Eg. Cancelled on 2013-01-08 only
      Report_Id |start_date | end_date   | 
  --------------+-----------+------------+-
      rep10001  | 2013-01-05| 2013-01-07 | 
                |           |            | 
  
  
  If there is no cancelled record, the result should be like below
      Report_Id |start_date | end_date   | 
  --------------+-----------+------------+-
      rep10001  | 2013-01-05| 2013-01-08 | 
                |           |            | 
  </code></pre>
  
  <h1>How do I query this?</h1>
  
  <p>Current Result (19 June 2018) Based on Mazhar's method.
  I have modified your query abit</p>
  
  <p>Query: </p>
  
  <pre><code>WITH cteX 
  AS(
  SELECT 
       Id = ROW_NUMBER()OVER(PARTITION BY D.request_no ORDER BY 
  D.leave_starttime ASC)
      ,a.leave_code
      ,a.emp_id
      ,D.request_no
      ,D.leave_starttime
      ,D.leave_endtime
      ,D.cancelsts
  FROM TTADLEAVEREQUEST a
  left join TTADLEAVEREQUESTDETAIL D on a.request_no = d.request_no
  where a.emp_id = 'emp1' and Year(a.leave_startdate) = 
  2018
  )
  SELECT 
   ReportId = 'rep1000' +  CAST(ROW_NUMBER()OVER(ORDER BY (SELECT NULL) ) AS 
  NVARCHAR(5))
  ,leave_starttime = MIN(Y.leave_starttime)
  ,leave_endtime = MAX(Y.leave_endtime)
  ,leave_code = Y.leave_code
  ,Count(Y.leave_starttime) as TOTAL_COUNT
  FROM (
  SELECT TOP 100 PERCENT
        Grp = Id - ROW_NUMBER()OVER( PARTITION BY X.cancelsts ORDER BY X.Id) 
       ,X.request_no
       ,X.leave_code
       ,X.leave_starttime
       ,X.leave_endtime
       ,X.cancelsts
  FROM 
      cteX X
  WHERE
      X.cancelsts = 'n'
  ORDER BY
      X.emp_id
  ) Y
  GROUP BY
  Y.Grp,Y.leave_code
  </code></pre>
  
  <p>==========================================================</p>
  
  <pre><code>LeaveRequest Table
  request_no||leave_code||emp_id||leave_startdate ||leave_enddate ||  
  ==================================================================
  1001      ||     AL   ||emp1  ||2018-01-29 00:00||2018-01-30 00:00
  1002      ||     AL   ||emp1  ||2018-02-01 00:00||2018-02-02 00:00    
  1003      ||     AL   ||emp1  ||2018-02-12 00:00||2018-02-13 00:00  
  
  LeaveRequestDetail Table
  request_no||    leave_starttime    ||  leave_endtime     ||  cancelsts
  =====================================================
  1001      ||     2018-01-29 08:00  ||  2018-01-29 17:00  ||    N
  1001      ||     2018-01-30 08:00  ||  2018-01-30 17:00  ||    N
  1002      ||     2018-02-01 08:00  ||  2018-02-01 17:00  ||    N
  1002      ||     2018-02-02 08:00  ||  2018-02-02 17:00  ||    N
  1003      ||     2018-02-12 08:00  ||  2018-02-12 17:00  ||    N
  1003      ||     2018-02-13 08:00  ||  2018-02-13 17:00  ||    N
  </code></pre>
  
  <p>Result from this query:</p>
  
  <pre><code>Result Table
  ReportID  || leave_starttime  ||  leave_endtime || leave_code || TOTAL_COUNT
  ======================================================================
  rep10001  ||    2018-01-30    ||  2018-01-30    ||      AL    ||         1
  rep10002  ||     2018-02-02   ||  2018-02-02    ||      AL    ||         1
  rep10003  ||     2018-02-12   ||  2018-02-13    ||      AL    ||         2
  rep10004  ||     2018-02-01   ||  2018-02-01    ||      AL    ||         1
  rep10005  ||     2018-01-29   ||  2018-01-29    ||      AL    ||         1
  </code></pre>
  
  <p>As you can see the result should only have 3 rows (eg. 29 Jan &amp; 30 Jan and 01 Feb &amp; 02 Feb supposed to be in single record). Really appreciate that you could help. Thanks.</p>