CODEWITHSUNDEEP
sql-servert-sqlwhere-clauseconditional-operator
poptard
poptard

Reputation: 61

SQL Server : conditional WHERE-clause

This is my first question so hope its all good!

What I have done is a user-defined (table valued) function using MSSMS, where in one point, I have to find records based on the functions parameter @Year.

Problem for me arises when records are older than two years, then it should list all the record which are older than two years. I have been banging my head on the wall with this one, and this is where I have gotten so far:

WHERE   (
        @Year = YEAR(GETDATE()) AND YEAR(E.ExampleColumnDate) = @Year
        OR
        @Year = YEAR(GETDATE())-1 AND YEAR(E.ExampleColumnDate) = @Year
        OR
        @Year = YEAR(GETDATE())-2 AND YEAR(E.ExampleColumnDate) = @Year
        OR
        @Year <= YEAR(GETDATE())-3 AND YEAR(E.ExampleColumnDate) < @Year
        )

Upvotes: 0

Views: 205

Answers (2)

Heena Chhatrala
Heena Chhatrala

Reputation: 242

you can get records are older than two years with this also

;with cte 
as
(
SELECT top 10 DATEDIFF(year,E.ExampleColumnDate, getdate()) as diff, * 
  FROM [yourtablename]
  )
  select * from cte where diff =2

Upvotes: 0

realnumber3012
realnumber3012

Reputation: 1062

WHERE   (
        (@Year = YEAR(GETDATE()) AND YEAR(E.ExampleColumnDate) = @Year)
        OR
        (@Year = YEAR(GETDATE())-1 AND YEAR(E.ExampleColumnDate) = @Year)
        OR
        (@Year = YEAR(GETDATE())-2 AND YEAR(E.ExampleColumnDate) = @Year)
        OR
        (@Year <= YEAR(GETDATE())-3 AND YEAR(E.ExampleColumnDate) < @Year)
        )

Upvotes: 1

Related Questions