Search code examples
sqlsql-servercomparisondateperiod

SQL date, period comparison

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).

Solution

  • 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)