Need help with a SQL query selecting date ranges from a table of period quarters
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)
select * from Periods where dateadd(qq,PeriodQuarter-1,dateadd(yy,PeriodYear -1900,0)) between @startDate and @endDate
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)
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.
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