CODEWITHSUNDEEP
sqlsql-serverwhere-clause
James Miller
James Miller

Reputation: 3

Filter via different dates in where clause

Here I've created a simple table using MS SQL

CREATE TABLE Students1 (
FirstName Nvarchar(100),
SecondName Nvarchar(100),
DOB Smalldatetime,
RegisterDate smalldatetime,
Archived int,
ArchivedDate Smalldatetime
);

Insert Into Students1(FirstName,SecondNAme,DOB,RegisterDate,Archived,ArchivedDate)
Values('James','Bike',16/04/1900, 04/07/2017,0,Null);
Insert Into Students1(FirstName,SecondNAme,DOB,RegisterDate,Archived,ArchivedDate)
Values('Adam','Bike',16/04/1901,04/07/2017,0,Null);
Insert Into Students1(FirstName,SecondNAme,DOB,RegisterDate,Archived,ArchivedDate)
Values('Chris','Bike',16/04/1902,04/09/2017,1,getdate());
Insert Into Students1(FirstName,SecondNAme,DOB,RegisterDate,Archived,ArchivedDate)
Values('Sam','Bike',16/04/1999,04/09/2017,1,getdate());
Insert Into Students1(FirstName,SecondNAme,DOB,RegisterDate,Archived,ArchivedDate)
Values('Josh','Bike',16/04/1999,04/09/2017,1,getdate());

I want to do a simple select statement against this where I'm filtering on the either the RegisterDate or the ArchivedDate depending on how my local variables are set up.

For instance, i will have the following local variables set; @FitlerRegisterDate @FilterArchived If either @FilterREgisterDate is set to 1 then the where clause will look at the RegisterDate column, but if the @FilterArchived is set to 1 then the where clause will look at the ArchivedColumn AND if they're both set to 1 then it should default to look at the RegisterDate

I've had a look at this for a while and cant see anything that stands out. If someone could point me in the right direction, that would be great.

Thanks.

Upvotes: 0

Views: 83

Answers (3)

prashant sugara
prashant sugara

Reputation: 321

select * from dbo.Students1 where (@FilterArchived=1 AND @FilterREgisterDate=1) and registerdate='04/07/2017'

UNION 

select * from dbo.Students1 where (@FilterArchived!=1 AND @FilterREgisterDate=1) and registerdate='04/07/2017'

UNION

select * from dbo.Students1 where  (@FilterArchived=1 AND @FilterREgisterDate!=1) and archived=1

Upvotes: 0

Flavian-Calinic Stuparu
Flavian-Calinic Stuparu

Reputation: 199

Something like this :

SELECT *
  FROM Students1
 WHERE @FilterREgisterDate = 1 AND RegisterDate = @RegisterDate
       OR @FilterREgisterDate <> 1 AND @FilterArchived = 1 AND ArchivedDate = @ArchivedDate
;

?

Upvotes: 0

Kobi
Kobi

Reputation: 2524

You can use AND and OR clause to filter according to your variable values:

SELECT *
FROM   dbo.Students1
WHERE  (    @FilterArchived     = 1 
        AND @FilterREgisterDate = 1 
        AND RegisterDate        = @dateToFilter )
  OR (   (    @FilterREgisterDate = 1 
          AND RegisterDate        = @dateToFilter )
      OR (    @FilterArchived = 1 
          AND ArchivedDate    = @dateToFilter ) )

Upvotes: 1

Related Questions