Case Statement Select

[jnavarro]

I a SP which I have different where clause however I am unable to find the error. Can you please help me or direction on how I can accomplished the same thing


REATE Procedure AIRetrieval @AssocNum bigint, @Criteria as char(1)
as
Select *
from tblAI
Where Case @Criteria
When 'P' then
AssocNum = @AssocNum and DateResolved is null
When 'C' then
AssocNum = @AssocNum and DateResolved is not null
When 'I' then
DateResolved is null
End

GO

 

[jnavarro]

i have not try that yet but I will let you know if it works

 

[jnavarro]

Yes, the code that was supplied work however it was not what I was looking for.
Trying to do a short cut actually created more work.
Thanks

 

[sk8er1]

Hi

I need to do the same thing, depending on what value the user selects, I want dates that are either NULL or NOT NULL,
Was Vongrunt's solution the correct one...Thanks all

Select * from tblAI
where
( ( @Criteria='P' and assocnum=@assocnum and DateResolved is null ) OR
( @Criteria='C' and assocnum=@assocnum and DateResolved is not null ) OR
( @Criteria='I' and DateResolved is null )
)

 

[sereleg]

I need some help to filter a record set using the greatest year:

My sql
select EMPLID,Action_DT from PROFILES.dbo.ps_job where Action in ('PRO','XPR') AND EMPLID in ('062497','105149')
ORDER by EMPLID des
I just place two EMPLID but could be thousands of them.

EXAMPLE

EMPLID date

105149 2005-03-28 00:00:00.000
105149 1997-02-28 00:00:00.000
105149 1998-12-23 00:00:00.000
105149 2003-04-11 00:00:00.000
105149 2000-04-04 00:00:00.000
062497 1992-05-01 00:00:00.000
062497 1998-03-04 00:00:00.000

I would like to obtain:
id date
105149 2005-03-28 00:00:00.000
062497 1998-03-04 00:00:00.000

If some one can help me I will appreciate it

Raul

 

[vongrunt]

Open new thread please...

FYI this looks like standard MAX()/GROUP BY thing...

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.