Seperate Records to share Date Range utilizing a single column of dates

Question

I have the following records from my table called Disbursements. What I like to do is break out each record into its separate record set of records based on the Beginning and Ending Service Date utilizing only 1 column of dates.

 DisbursementID ServiceProviderID Original CircuitID   Beginning_Service_Date Ending_Service_Date Amount
 -------------- ----------------- -------- ----------- ---------------------- ------------------- -----------
 53562          673               0        1814        2015-12-01             2015-12-31          531
 53563          673               0        1814        2015-11-01             2015-11-30          531

My GOAL Result is to look like

 DisbursementID ServiceProviderID Original CircuitID   Date Range  Amount
 -------------- ----------------- -------- ----------- ---------- -------
 53562          673               0        1814        2015-12-01 531
 53562          673               0        1814        2015-12-02 531
 53562          673               0        1814        2015-12-03 531
 53562          673               0        1814        2015-12-04 531
 53562          673               0        1814        2015-12-05 531
 53563          673               0        1814        2015-11-01 531
 53563          673               0        1814        2015-11-02 531
 53563          673               0        1814        2015-11-03 531
 53563          673               0        1814        2015-11-04 531
 53563          673               0        1814        2015-11-05 531

Instead my result looks like

 DisbursementID ServiceProviderID Original CircuitID   Date Range  Amount
 -------------- ----------------- -------- ----------- ---------- -------
 53562          673               0        1814        2015-12-01 531
 53563          673               0        1814        2015-11-01 531
 53563          673               0        1814        2015-11-02 531
 53563          673               0        1814        2015-11-03 531
 53563          673               0        1814        2015-11-04 531
 53563          673               0        1814        2015-11-05 531

The following is a piece of code that I found but adapted to my needs. It almost solves my problem but I cant figure out how to include the block of range dates from my first record. I know why it does it, but do not know how to properly fix it:

 ;With Dates as
 ( 
 Select DisbursementID, ServiceProviderID,Original,CircuitID
 ,Beginning_Service_Date as BeginDate, Ending_Service_Date as EndDate
 ,Amount From Disbursement

 Union All

 Select DisbursementID, ServiceProviderID, Original,CircuitID
 ,DATEADD(day,1,BeginDate) as CalenderDate, EndDate
 ,Amount 
 From Dates
 Where DATEADD(day,1,BeginDate) <= EndDate
 )

 Select DisbursementID, ServiceProviderID,Original,CircuitID
,BeginDate as [Date Range], Amount from Dates
Order By CircuitID
Option (MAXRECURSION 366);

Answer 1

Using a recursive cte is one of the worst ways to generate a range of dates. The answer by John Cappelletti is much better for generating the date range on demand than using a recursive cte.

If you are going to be using it across 55,000+ rows, and you will be running this sort of operation more than once, you would be better off just creating a Dates or Calendar table.

For only 152kb in memory, you can have 30 years of dates in a table, and you could use it like so:

/* dates table */ 
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo 
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date 
  on dbo.Dates([Date]);

And use the Dates table like so:

select 
    t.DisbursementID
  , t.ServiceProviderID
  , t.Original
  , t.CircuitID
  , d.[date]
  , t.Amount
from t
  inner join dates d
    --on d.date >= t.Beginning_Service_Date
   --and d.date <= t.Ending_Service_Date
  /* if you want to have the date range work when 
      Beginning_Service_Date and Ending_Service_Date are backwards
      you could use between */
    on d.date between t.Beginning_Service_Date
                  and t.Ending_Service_Date

rextester demo: http://rextester.com/WNMJW41879

Number and Calendar table reference:

• Generate a set or sequence without loops - 3 - Aaron Bertrand
• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones

Answer 2

If you don't have or can't use a Tally/Calendar Table, another approach would be to use an ad-hoc tally table.

Declare @YourTable table (DisbursementID int, ServiceProviderID int, Original int, CircuitID int, Beginning_Service_Date date, Ending_Service_Date date, Amount int)
Insert Into @YourTable values
 ( 53562,673,0,1814,'2015-12-01','2015-12-31',531)
,( 53563,673,0,1814,'2015-11-01','2015-11-30',531)

;with cte1 as (
                 Select MinDate=min(Beginning_Service_Date)
                       ,MaxDate=max(Ending_Service_Date)
                 From @YourTable )
     ,cte2 as (
                 Select Top (DateDiff(DD,(select MinDate from cte1),(select MaxDate from cte1))+1) 
                        D = DateAdd(DD,-1+Row_Number() Over (Order By (Select null)),(select MinDate from cte1)) 
                  From  master..spt_values A -- ,master..spt_values B  -- If you need more than 6 years
 )
Select A.DisbursementID 
      ,A.ServiceProviderID 
      ,A.Original 
      ,A.CircuitID 
      ,[Date Range] = B.D
      ,A.Amount
 From  @YourTable A
 Join cte2 B on B.D between A.Beginning_Service_Date and A.Ending_Service_Date