Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Every now and then, bite off more than you can chew
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

13
May

How Can I Get All the Dates between 2 Parameter Dates

During an SQL Server Reporting Services consultancy, the client needed to see the performance of the sales team over a period of time.

The client wanted to see if there was any trends, with regards to whether the sales personnel was meeting their SLA of their client.


Solution:

The way to achieve this was to create an SQL temporary table that listed all the days between the two parameter dates.

There are many ways of creating this temporary table, but I decided to use a SQL CTE (Common Table Expression) syntax:

Declare @StartDate as DateTime
Declare @EndDate as DateTime

Set @StartDate = '10/30/2016'
Set @EndDate = '12/11/2016'

;WITH Dates AS (
        SELECT
         [Date] = @StartDate
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         [Date] <= @EndDate
) SELECT
 [Date]
FROM
 Dates
 OPTION (MAXRECURSION 100);
 
This CTE table can be linked, using a left outer join, to the Sales/Orders table using the dates fields.


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

66 + 25 =