SQL date, period comparison

Question

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 a SQL query 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 for which the startDate is the same. But all the rows which are actually within the range of the new 'period' should be returned, as well.

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 basically would need a little help on the logic to build up the SQL query.

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

I'm working with SQL Server (but I think non-dialect SQL can do the trick, as well).

Gabriel McAdams · Accepted Answer · 2009-12-29T23:27:18.660

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)

edited Dec 29 '09 at 23:27

answered Dec 29 '09 at 21:50

Gabriel McAdams

56,921
12
61
77

the periode with 2010-01-04 as start date has a duration of 3 weeks so it's still running in the week of 2010-01-11 so it def. needs to return this row aswell. And weeks who have the same start date should always be returned no matter what – Dec 29 '09 at 22:13
1

So I should understand that you want returned: Any records where any date in the range is within the date range passed in? (date range = start date to the calculated date using the number of weeks) – Gabriel McAdams Dec 29 '09 at 22:42
yes and a period always starts on a monday and ends on a sunday. So when I give a start date and an amount of weeks as parameters I should get the rows returned who are within the period of the parameters – Dec 29 '09 at 22:47
your solution is correct, one little detail. DATEADD(DAY,(@numWeeksParam*7) - 1,@startParam). Thank you for your time – Dec 30 '09 at 12:16

score 1 · Answer 2 · answered Dec 29 '09 at 21:54

1

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)

answered Dec 29 '09 at 21:54

Charles Bretana

143,358
22
150
216

score 0 · Answer 3 · answered Dec 29 '09 at 22:30

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.

SQL date, period comparison

3 Answers3