Need help with a SQL query selecting date ranges from a table of period quarters

Question

Hi.

I have a table called Periods that looks like this

PeriodID | PeriodYear | PeriodQuarter

7 | 2009 | 1

8 | 2009 | 2

9 | 2009 | 3

10 | 2009 | 4

11 | 2010 | 1

12 | 2010 | 2

Each row in the table represents 1 of the 4 quarters of the year (like 3-monthly school terms). E.g. The first row represents Period 1 of 2009 (i.e. the date range 1 Jan 2009 - 31 March 2009.

Now I need to write a query that selects rows/periods from the above table, where the period occurs between 2 date ranges, as per the following pseudocode.

select *
from Periods
where Period is between @startDate and @endDate

The query will be used inside a table-valued function called dbo.GetPeriodsFromDateRange, and @startDate and @endDate are parameters to the function.

I'm stuck and can't figure out how to do it. Please help. This applies to T-SQL (MS SQL Server 2000/2005)

Answer 1

I would be tempted to add 2 further columns to the table...

StartDate and EndDate - these will store the date that each period starts and ends (i.e. in your example StartDate=1st Jan 2009 and EndDate=31st March 2009)

This will give you more flexibility if the quarters are defined differently than you have suggested.

If you do this, then the query become fairly simple...

select *
from Periods
where @startDate<Periods.StartDate and @endDate>Periods.EndDate

This is assuming you only want to include Periods which are completely encapsulated between @StartDate and @EndDate. If you want Periods that overlap then try something like...

select *
from Periods
where @EndDate>Periods.StartDate and @StartDate<Periods.EndDate

Answer 2

Try

select *
from Periods
where  dateadd(qq,PeriodQuarter-1,dateadd(yy,PeriodYear -1900,0)) 
between @startDate and @endDate

Answer 3

A seek instead of a scan is possible:

SELECT *
FROM Periods
WHERE
   PeriodYear BETWEEN Year(@startdate) AND Year(@enddate)
   AND PeriodYear * 4 + PeriodQuarter
      BETWEEN Year(@startdate) * 4 + DATEPART(Quarter, @startdate)
      AND Year(@startdate) * 4 + DATEPART(Quarter, @enddate)

Explanation:

I'm composing a new, scaled integer from two component pieces, the year and the quarter, treating each combination of year and quarter as a single number.

Imagine instead that I had done it this way:

AND PeriodYear + (PeriodQuarter - 1) / 4.0
   BETWEEN Year(@startdate) + (DATEPART(Quarter, @startdate) - 1) / 4.0
   AND Year(@startdate) + (DATEPART(Quarter, @enddate) - 1) / 4.0

Calling my original expression "Mult" and this new one "Div", here are some years and quarters and what those expressions will evaluate to:

Year Qtr Div     Mult
2009 1   2009.00 8037
2009 2   2009.25 8038
2009 3   2009.50 8039
2009 4   2009.75 8040
2010 1   2010.00 8041
2010 2   2010.25 8042
2010 3   2010.50 8043

So now if we run a WHERE clause against these rows:

WHERE Div BETWEEN 2009.25 AND 2010.00

You can see how it will return the correct rows. The Mult version really does exactly the same, just scaling the year up instead of the quarter down. The reason I used it is because integer math and multiplication are faster than fractional math and division.

The reason that I use two conditions starting with just the year is to make the query sargable. We want to do the seek based on just year, which isn't possible if we're multiplying it by 4 or doing other math on it. So we get the scan into only the right years first, then fine tune it to eliminate any quarters that shouldn't be in the result.

Another option is to add a calculated column and put an index on it. This wouldn't require any changes to code inserting or updating (as long as they properly use column lists), but would let you do regular range math as you desire.