Help using case statement in a where clause

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help using case statement in a where clause

Author

Topic

polygonSQL
Starting Member

8 Posts

Posted - 2005-03-23 : 13:19:48

The reporting software my company uses doesn't have a time feature. The client that I'm building a report for wants the results for morning or afternoon. The <<19>> is a prompt for the client when running the report. Can anyone help me to get this query to work. I'm not placing the entire query here because I just need basic help.

Below are the two queries I've tried:

declare @N int
set @N = 1--<<19|1 Morning 2 Afternoon>>
select * from schedule
where (case
when @N = 1 then schstarttime < '1899-12-30 12:00:00.000'
else schstarttime > '1899-12-30 12:00:00.000'
end)

declare @N int
set @N = 1--<<19|1 Morning 2 Afternoon>>
select * from schedule
where schid in (case
when @N = 1 then (select S9.schid from schedule S9 where schstarttime < '1899-12-30 12:00:00.000')
else (select S9.schid from schedule S9 where schstarttime > '1899-12-30 12:00:00.000')
end)

Any direction would be much appreciated. TIA
Poly

-----------------------
select * from end_user
where clue > 0
GO
( 0 rows returned )

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 13:48:39

Why not


CREATE PROC mySproc99(@Start datetime, @end datetime)
AS
	SELECT * FROM Schedule
	WHERE schstarttime > @Start AND schstarttime < @End
GO

And what about the days? Why did you break out time and date?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources