Charles Bernardes Charles Bernardes -4 years ago 94
SQL Question

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

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download