SQL Server : CTE Months between dateranges

Question

I have a table with the following structure:

StartDate datetime NOT NULL
EndDate datetime NOT NULL
EnrollmentId nvarchar(255) NOT NULL
ProgrammeId nvarchar(255) not null

EnrollmentId is the primary key, and is a bit like a GUID string, and ProgrammeId refers to a 'subject code' a bit like a GUID too.

What I want to do is determine the total number of months a Programme has accrued during a specific date range for ALL enrollments e.g. from 01/01/2011 to 1st April 2014.

So let's say I have an enrollment which exists between 08/08/2010 to 01/01/2012 for a particular programmeId. I would want to count all the months that had accrued after 01/01/2011. So any months prior to this date should not count i.e. 08/08/2010 to 31/12/2010 should not be counted.

Ideally I'd like a count of how many months per ProgrammeId for a given date range.

Just a few notes:

I would need to count whole months so if an enrollment begins at 08/01/2011 and ends at 20/01/2012 only 12 months should be considered.
The period in which I want to count the months usually are at the beginning of the respective month i.e. 1st of jan 2011 to 1st of Apr 2014, 1st of May 2014 etc etc.

Some code to return all months for an enrollment:

;with MonthList as (
    select 
        DATEADD(month, M, '12/1/1899') as 'FirstDay',
        dateadd(day, -1, dateadd(month, M + 1, '12/1/1899')) as 'LastDay',
        DATEADD(month, M + 1, '12/1/1899') as 'FirstDayNextMonth'
    from (
        select top 3000 ROW_NUMBER() over (order by s.name) as 'M'
        from master..spt_values s) s
)

select
  t.ProgrammeId, ml.FirstDay, ml.LastDay
from
    Enrollment t
    inner join MonthList ml
        on  t.startdate < ml.FirstDayNextMonth
            and t.enddate >= ml.FirstDay

Thanks in advance

Title says CTE, but your not asking anything about a CTE. Have you tried to do this yourself using a CTE? — Karl Kieninger, Apr 26 '14 at 03:57
Yes I have tried to build a CTE function which displays all the months per Enrollment. But this doesn't really resolve my problem as I am including the first and final month of each enrolment which may or may not be included depending on the start date and end date. My assumption is you would need a CTE function for this, but I have seen something similar to my CTE function using a CROSS APPLY. — TonyPepperoni, Apr 26 '14 at 04:00
You should post your CTE here. And some sample data. And the desired output for your sample. That makes is much easier for folks to help. — Karl Kieninger, Apr 26 '14 at 04:13

score 0 · Answer 1 · answered Apr 26 '14 at 04:46

I thought I had an answer for you, but your update confused me as to what you actually wanted. I'm including the statement I wrote (SQL Fiddle) in the hope that it will be helpful.

SELECT ProgrammeId, 
       DATEDIFF(MONTH, 
                 CASE WHEN '2011-01-01 00:00:00' > MIN(StartDate) THEN '2011-01-01 00:00:00'
                      ELSE MIN(StartDate)
                  END,
                 CASE WHEN '2014-04-01 00:00:00' < MAX(EndDate) THEN '2014-04-01 00:00:00'
                      ELSE MAX(EndDate)
                  END
               ) As Months
  FROM ProgrammeEnrollment
 GROUP BY ProgrammeID
;

SQL Server doesn't have GREATEST and LEAST, so I had to improvise with CASE statements. If you were using a RDBMS that did have GREATEST and LEAST, they'd be drop in replacements for the CASE statements.

score 0 · Accepted Answer · edited May 23 '17 at 12:05

You may need a function like the one in Calculating number of full months between two dates in SQL to get the full months.
assuming you have something like that I would filter to make sure you are in range and chip the start and the end to fit the range in a CTE as you suggest. 3.The grouping and summing is easy.

Set up some sample data:

CREATE TABLE #tbl (
  EnrollmentId INT NOT NULL --NOTE: using INT instead of your VARCHAR becuas eit easier and doesn't matter in sample
 ,ProgrammeId INT NOT NULL
 ,StartDate DATETIME NOT NULL 
 ,EndDate DATETIME NOT NULL
)

INSERT INTO #tbl VALUES
(1,1,'2013-01-01','2014-01-01'),
(2,1,'2013-07-01','2014-01-01'),
(3,2,'2013-01-01','2014-01-01')
(4,3,'2013-01-15','2014-03-01')

Now declare the search range and make the query

DECLARE @RangeStart DATETIME = '2013-01-01'
DECLARE @RangeEnd DATETIME = '2013-12-01'

;WITH cte AS (
SELECT EnrollmentId
      ,ProgrammeId
      ,CASE WHEN @RangeStart >= StartDate THEN @RangeStart ELSE StartDate END EffectStartDate
      ,CASE WHEN @RangeEnd <= EndDate THEN @RangeEnd ELSE EndDate END EffectEndDate

  FROM #tbl
 WHERE @RangeStart BETWEEN StartDate AND EndDate --start date is in range
    OR @RangeEnd BETWEEN StartDate AND EndDate --or end date is in range
    OR (EndDate > @RangeEnd AND StartDate < @RangeStart) --or period contains the range
 )
 SELECT ProgrammeId
       ,SUM(dbo.FullMonthsSeparation(EffectStartDate,EffectEndDate)) Months
   FROM cte
  GROUP BY ProgrammeId

Results from sample:

ProgrammeId Months
----------- -----------
1           16
2           11
3           10

This looks like what I need, however I'm confused at this point: ` WHERE @RangeStart BETWEEN StartDate AND EndDate OR @RangeEnd BETWEEN StartDate AND EndDate` — TonyPepperoni, Apr 26 '14 at 05:35
Once I remove it I am getting something resembling correct results. Hmm — TonyPepperoni, Apr 26 '14 at 05:37
That WHERE condition to should filter your table to only rows that are actual in the range. But it is wrong and I;ll correct. — Karl Kieninger, Apr 26 '14 at 05:41

SQL Server : CTE Months between dateranges

2 Answers2