1

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);
Improve this question

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you. I dont have a TALLY/CALENDAR table and I have no idea how the CROSS APPLY works, but will look into it now.
Oh I like your edited Another Option solution. My ETL process that utilizes SQL code ran into problems using your first solution, but your EDITED Solution, It took it a lot better.
@CharlesBernardes Odd that you had an issue with the first. I got the same results with both. Keep the CROSS APPLY in your back-pocket. They are useful... think of them as a sub-routine
When I ran it in SSMS against 55,000 records, it eventually gave me an error message Msg 127, Level 15, State 1, Line 1 A TOP N or FETCH rowcount value may not be negative.
I thought your solution was working till I ran a few tests. It seems not all records are not all being returned. Is their a way I can send you a personal e-mail? Otherwise, I am not sure how I can give you a copy of all the records from my table to show you what it is not returning. I have over 50,000 records in my table. I dont understand your code enough to find out why it is acting in such a manner.
|
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:

Improve this answer

4 Comments

you are correct that it is a huge performance issue. I do notice that. He (John) did ask if I had a Calendar type of table and I didnt. So he didnt provide his initial answers based on that. Plus, I didnt know how to create one to use it properly against my code I provided. But you did and gave me another alternate way of looking at this. I appreciate this a lot.
@CharlesBernardes John's answer is a good one (I upvoted it as well), but the comment about using it over 55,000 rows was a good enough reason for me to offer this alternative.
I gave john the points for the answer, since he did give me a solution that did work according to my initial post. Since you followed it up with a better alternative, I wish I could give you credit to an answer too. I hope you are not offended. I did give you an upvote too.
@CharlesBernardes Not at all, as I said before John's answer is a good one. An upvote is plenty if you found my answer worthy of consideration as an alternative.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.