Using Case & Where - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Case & Where

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2011-04-18 : 07:17:26

Hi All,

I have the following table

TABLE1 (NAME, STATUS,DateReported)

I want to select all the records from TABLE1 but if the STATUS is 1 then I will only get the records with Date Difference < 45, for the other values of STATUS I want to get all the records

I was thinking to use something like this

SELECT * FROM TABLE1
WHERE
DATEDIFF(DAY, dbo.TABLE1.DateReported, GETDATE()) <
CASE Status
WHEN 2 THEN 45
ELSE ???
END

My question is what I need to put for the ELSE part of the CASE. I want to get all the records so I do not know what is the DateDiff value

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 07:24:49

[code]SELECT
*
FROM
TABLE1
WHERE
STATUS <> 1
OR DATEDIFF(DAY, dbo.TABLE1.DateReported, GETDATE()) < 45;
[/code]

mmalaka
Starting Member

33 Posts

Posted - 2011-04-18 : 07:39:08

quote:

Originally posted by sunitabeck

SELECT
	*
FROM
	TABLE1
WHERE
	STATUS <> 1 
	OR DATEDIFF(DAY, dbo.TABLE1.DateReported, GETDATE()) < 45;

This is did not work....It is not returing all the records....I think coz of the records is with NULL value for STATUS

What I am looking for is
If the status = 2 then get the records within 45 days otherwise get all the records

Any advice please?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 07:56:30

1 or 2?

SELECT
*
FROM
TABLE1
WHERE
STATUS <> 2
OR STATUS IS NULL
OR DATEDIFF(DAY, dbo.TABLE1.DateReported, GETDATE()) < 45;

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources