CODEWITHSUNDEEP
sql
SparX23
SparX23

Reputation: 63

SQL query to search between two weeks from different years

I have a column with week number and a column with year number. I want to search into database for a time interval, for example:

If I want to search between a week from this year, and a week from the next year, what query should I use?

If the year was the same that would be easy and my query will look like:

Select *
From mytable
Where (Finish_Week BETWEEN @Finish_Week1 AND @Finish_Week2) 
       AND (Finish_Year = @Finish_Year)

Upvotes: 1

Views: 1472

Answers (3)

Gidil
Gidil

Reputation: 4137

Simple answer:

SELECT * 
FROM   MYTABLE 
WHERE  ( FINISH_WEEK >= @Finish_Week1 
         AND FINISH_YEAR = @Finish_Year - 1 ) 
        OR ( FINISH_WEEK <= @Finish_Week2 
             AND FINISH_YEAR = @Finish_Year )

This solution assumes @Finish_Year is the second year.

Short explanation:

  • First condition - if the year is the first year (@Finish_Year - 1) and the week is above or equal to the week in the first year.
  • Second Condition - if the year is the second year (@Finish_Year) and the week is before or equal to the week in the second year.

Upvotes: 2

roman
roman

Reputation: 117380

I think if you have indexes on Finish_Week, Finish_Year than best method is to write

select *
from mytable
where 
   (
       Finish_Year = @Finish_Year and Finish_Week >= @Finish_Week1 or
       Finish_Year = @Finish_Year + 1 and Finish_Week <= @Finish_Week2
   )

If you don't have indexes, you may consider convert weeks and year into dates and then compare dates.

Upvotes: 0

Barmar
Barmar

Reputation: 781048

This solution works for any range of years, they don't have to be one year apart like the other answers. It canonicalizes the year+week into a linear format that can be compared directly.

SELECT * 
FROM mytable
WHERE Finish_Year*53+Finish_Week BETWEEN @Finish_Year1*53+@Finish_Week1 AND @Finish_Year2*53+@Finish_Week2

If that's too slow (because there are many rows in the table) and you have an index on Finish_Year, add the following:

AND Finish_Year BETWEEN @Finish_Year1 AND @Finish_Year2

Upvotes: 0

Related Questions