Convert to single case statement

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 2005 Forums

Transact-SQL (2005)

Convert to single case statement

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-26 : 07:08:41

How to convert the two case statement in to single case statement

CASE
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.StartKMs
END
BETWEEN @StartKMs
AND @EndKms

CASE
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.EndKms
END
BETWEEN @StartKMs
AND @EndKms

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-26 : 07:24:39

Try

CASE
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.StartKMs
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.EndKms
END
BETWEEN @StartKMs
AND @EndKms

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-26 : 08:08:35

quote:
Originally posted by madhivanan

Try

CASE
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.StartKMs
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.EndKms
END
BETWEEN @StartKMs
AND @EndKms

Madhivanan

Failing to plan is Planning to fail

SORRY I HAD MISSED OR INBETWEEN TWO CASE

SELECT * FROM
TEST
WHERE

CASE
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.StartKMs
END
BETWEEN @StartKMs
AND @EndKms

OR

CASE
WHEN @StartKMs <> VRL.EndKms
AND @EndKms <> VRL.StartKMs THEN
VRL.EndKms
END
BETWEEN @StartKMs
AND @EndKms

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 09:07:17

[code]SELECT *
FROM TEST
WHERE (@StartKMs <> VRL.EndKms AND @EndKms <> VRL.StartKMs AND VRL.StartKMs BETWEEN @StartKMs AND @EndKms)
OR (@StartKMs <> VRL.EndKms AND @EndKms <> VRL.StartKMs AND VRL.EndKms BETWEEN @StartKMs AND @EndKms)[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 09:15:10

[code]SELECT *
FROM TEST
WHERE (
VRL.StartKMs >= @StartKMs AND VRL.StartKMs < @EndKms
OR
VRL.EndKms > @StartKMs AND VRL.EndKms <= @EndKms
)[/code]

E 12°55'05.63"
N 56°04'39.26"

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-27 : 00:30:34

quote:

Originally posted by Peso

SELECT	*
FROM	TEST
WHERE	(
		VRL.StartKMs >= @StartKMs AND VRL.StartKMs < @EndKms
	OR
		VRL.EndKms > @StartKMs AND VRL.EndKms <= @EndKms 
	)

E 12°55'05.63"
N 56°04'39.26"

No I want only in the case

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 02:30:11

[code]SELECT *
FROM TEST
WHERE CASE
WHEN VRL.StartKMs >= @StartKMs AND VRL.StartKMs < @EndKms THEN 1
WHEN VRL.EndKms > @StartKMs AND VRL.EndKms <= @EndKms THEN 1
ELSE 0
END = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources