CODEWITHSUNDEEP
sqlsql-servert-sql
Website Builder
Website Builder

Reputation: 17

How to find a particular year within a year range in SQL

I am passing in a year, e.g. 2010

I have two columns in my table, yearFrom and YearTo

yearFrom            yearTo
2009                2010        *hit
2005                            *miss
2010                2015        *hit
2015                2018        *miss
2010                            *hit

Where hit is the row selected, and miss is it not.

Sometimes there will be a yearTo value in the column, where there isn't it will be null.

How do I formulate a where clause to accomplish this.

I have tried 

and yearFrom >= @year and yearTo <= @year

but it doesn't return what I need.

Upvotes: 0

Views: 92

Answers (4)

HABO
HABO

Reputation: 15816

yearFrom <= @Year and @Year <= Coalesce( yearTo, yearFrom ).

If yearTo is NULL then it will use yearFrom for the second comparison making it a single model year part.

Upvotes: 1

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14198

You can try this way, demo on db<>fiddle

SELECT *
FROM Table1
WHERE (yearTo IS NULL AND yearFrom = @year) 
      OR (yearFrom <= @year and @year <= yearTo)

Output

yearFrom    yearTo
2009        2010
2010        2015
2010        null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you have the logic backwards:

and yearFrom <= @year and yearTo >= @year

Upvotes: 0

Nick
Nick

Reputation: 147146

This query will give you the results you want. It looks for rows where @year is between yearFrom and yearTo, or if yearTo is NULL, then that yearFrom is the same as @year:

SELECT *
FROM Table1
WHERE @year BETWEEN yearFrom AND yearTo
   OR yearTo IS NULL AND yearFrom = @year

Output:

yearFrom    yearTo
2009        2010
2010        2015
2010        (null)

Demo on SQLFiddle

Upvotes: 1

Related Questions