SQL date,period comparison

Question

Hi

I'm having some problem with logic of the comparison of some periods. I have a table in my database that looks like this:

Id|startDate  |amount of weeks|
--------------------------------
A1|2010-01-04 | 3
B3|2010-01-11 | 2

all the startDates start on the same day of the week (Monday)

now I need to write an sql where I have 2 parameters( a start date, and the amount of weeks of a new 'period') and I need to check and return all the rows whom startdate is the same. But all the rows who are actually within the range of the new 'period' should be returned aswell

Some examples to clarify this:

- when I give the following parameters (2010-01-04,1) I would need to have row 1 with id A1 returned
- (2010-01-11,1) ---> return A1,B3
- (2009-12-28,1) ---> return nothing
- (2009-12-28,2) ---> return A1
- (2010-01-18,1) ---> return A1,B3

Now I know how to work with parameters etc, so I basicly would need a little help on the logic to build up the sql.

SELECT Id FROM table WHERE startDate={0} or startDate={1} .....

I'm working on a SQL server (but I think non dialect sql can do the trick as well)

Answer 1

This will work:

SELECT
    ID
FROM table
WHERE startDate = @startParam
AND DATEADD(WEEK, [amount of weeks], startDate) < DATEADD(WEEK, @numWeeksParam, @startParam)

EDIT: I misunderstood your question. this should be a working solution:

SELECT
    ID
FROM TABLE
WHERE startDate BETWEEN @startParam AND DATEADD(WEEK, @numWeeksParam, @startParam)
OR DATEADD(WEEK, [amount of weeks], startDate) BETWEEN @startParam AND DATEADD(WEEK, @numWeeksParam, @startParam)
OR @startParam BETWEEN startDate AND DATEADD(WEEK, [amount of weeks], startDate)
OR DATEADD(WEEK, @numWeeksParam, @startParam) BETWEEN startDate AND DATEADD(WEEK, [amount of weeks], startDate)

Answer 2

try this

 Select t.*
 From Table T
 Where @StartDate 
         Between t.StartDate And DateAdd(week, t.AmtWeeks, T.StartDate)
    or DateAdd(week, @AmtWeeks, StartDate) 
         Between t.StartDate And DateAdd(week, t.AmtWeeks, T.StartDate)

Answer 3

Since your question is generic, and others can end up here with slightly different requirements:

Surely SQL Server has an interval-type? That would make the provided queries simpler, and you could handle arbitrary intervals, not just weeks. I'm not experienced with SQL Server --- how will it fare with those int-to-interval-casts wrt. to indexes? If a functional index is required, you might as well store a triple of dates. That'll also let you handle a possible future requirement of different before- and after-intervals.