CODEWITHSUNDEEP
sql
Naveenkumar
Naveenkumar

Reputation: 1

finding records relative to today

I have a table named dbstorage which has columns like year and weekno.

I need to select the year and weekno which is less than current year and current week.

I used:

select * from dbstorage where year<=2011 and weekno<=13.

But it is not giving the correct data like if I have a value year:2010 and weekno:25 means, the query is not selecting this record.

How can this be done?

Upvotes: 0

Views: 104

Answers (3)

Martin Smith
Martin Smith

Reputation: 453028

You can split it out into two cases and use or

SELECT * /*<-- But don't use * List the columns explicitly*/
FROM   dbstorage
WHERE  ( year = 2011
         AND weekno <= 13 )
        OR year < 2011

Edit

But And logic may well be more efficient

SELECT *
FROM   dbstorage
WHERE  ( year <= 2011 )
       AND ( year < 2011
              OR weekno <= 13 )

Upvotes: 1

JNK
JNK

Reputation: 65147

For SQL Server you can also do:

SELECT *
FROM dbstorage
WHERE (year = DATEPART(yyyy, GETDATE())
      AND weekno <= DATEPART(ww, GETDATE()))
OR year < DATEPART(yyyy, GETDATE())

Upvotes: 0

vlad
vlad

Reputation: 4778

you have to split up the search into multiple parts:

select * from dbstorage 
where
    (year = 2011 and weekno <= 13) -- will catch all weeks in 2011 PRIOR to this one
    or (year < 2011) -- will catch all weeks in 2010 and earlier

Upvotes: 0

Related Questions