I am modifying a SSRS sales report for my customer. I have already calculated the number of days and based on that the daily value. In the software you set the start and end date of the agreement which are visible on the report.

My customer wants to split the agreement value in to individual date values. Is there a way how I can split the date range in to individual dates? Then I would just group all the individual dates values from all agreements in to one daily total.

I am using SQL Server 2017 and Visual Studio 2013

enter image description here


1 Answer 1

WITH cte AS ( SELECT {columns list}, start_date as single_date 
              FROM source_table
            UNION ALL
              SELECT {columns list}, DATEADD(day, 1, single_date)
              FROM cte
              WHERE single_date  < finish_date )
FROM cte;

Example fiddle

  • Thanks, worked like a charm! I just had to add "option (maxrecusrion 0) as there are so many lines generated Oct 2, 2020 at 11:14
  • @JuhaKalliola Do it NEVER until you guarantee that recursion will stop after reasonable amount of iterations. In your case - imagine there exists errorneous row with finish_date in far future... it's better to use, for example, something like option (maxrecusrion 1100) - this will generate the dates list not more than for 3 years but will stop with guarantee.
    – Akina
    Oct 2, 2020 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.