Case Statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statement

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-24 : 09:33:15

I have a case statement in my query for where condition.I tried to write it 2 ways but both of them are performance hell.Is there a better way???


1st Case
CASE WHEN (DPM.EndDate IS NULL) 

THEN 
		CASE WHEN (Cn.SaleDate >= DPM.StartDate) 
			THEN 1
		ELSE 
      0
		END
 ELSE
 CASE WHEN (Cn.SaleDate BETWEEN DPM.StartDate AND DPM.EndDate) THEN 1 
ELSE 0 END 
END = 1 

2nd Case
1=CASE WHEN (DPM.EndDate IS NULL) and (Cn.SaleDate >= DPM.StartDate) THEN 1 

     WHEN (Cn.SaleDate BETWEEN DPM.StartDate AND DPM.EndDate) THEN 1 
     else 0
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:46:12

WHERE cn.SaleDate BETWEEN dpm.StartDate AND COALECE(dbp.EndDate, cn.SaleDate)

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-24 : 09:59:05

Thanks for the reply Peso.
My condition is I want to nullify the "and" conditions before my case statement.Say


Name='sa' and
1=CASE WHEN (DPM.EndDate IS NULL) and (Cn.SaleDate >= DPM.StartDate) THEN 1 

     WHEN (Cn.SaleDate BETWEEN DPM.StartDate AND DPM.EndDate) THEN 1 
     else 0
end

If the case condition comes true i.e 1=1 the condition name='sa' will come into picture else it wont if it is false.I doubt your solution will fulfill that.Sorry if I am wrong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 10:04:26

WHERE cn.SaleDate BETWEEN dpm.StartDate AND COALESCE(dbp.EndDate, cn.SaleDate)
AND name = 'sa'

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 10:05:37

quote:
Originally posted by ayamas

I doubt your solution will fulfill that.Sorry if I am wrong.

Try and leave feedback here afterwards.

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-24 : 10:09:40

Its not working.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 10:40:13

quote:
Originally posted by ayamas

Its not working.

You tell me what the difference in output is!

DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1),
		Name VARCHAR(20),
		StartDate DATETIME,
		EndDate DATETIME,
		SaleDate DATETIME
	)

INSERT	@Sample
SELECT	'sa', '20090801', '20090830', '20090815' UNION ALL
SELECT	'sa', '20090801', '20090830', '20090915' UNION ALL
SELECT	'sa', '20090801',       NULL, '20090815' UNION ALL
SELECT	'ayamas', '20090801', '20090830', '20090815' UNION ALL
SELECT	'ayamas', '20090801', '20090830', '20090915' UNION ALL
SELECT	'ayamas', '20090801',       NULL, '20090815'

-- Peso
SELECT	*
FROM	@Sample
WHERE	SaleDate BETWEEN StartDate AND COALESCE(EndDate, SaleDate)
	AND Name = 'sa'

-- Ayamas 1
SELECT	*
FROM	@Sample
WHERE	CASE
		WHEN EndDate IS NULL THEN	CASE
							WHEN SaleDate >= StartDate THEN 1
							ELSE 0
						END
		ELSE	CASE
				WHEN SaleDate BETWEEN StartDate AND EndDate THEN 1 
				ELSE 0
			END 
	END = 1
	AND Name = 'sa'

-- Ayamas 2
SELECT	*
FROM	@Sample
WHERE	CASE
		WHEN EndDate IS NULL AND SaleDate >= StartDate THEN 1 
		WHEN SaleDate BETWEEN StartDate AND EndDate THEN 1 
		ELSE 0
	END = 1
	AND Name = 'sa'

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-24 : 10:55:01

quote:

Originally posted by Peso

quote:
Originally posted by ayamas

Its not working.

You tell me what the difference in output is!

DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1),
		Name VARCHAR(20),
		StartDate DATETIME,
		EndDate DATETIME,
		SaleDate DATETIME
	)

INSERT	@Sample
SELECT	'sa', '20090801', '20090830', '20090815' UNION ALL
SELECT	'sa', '20090801', '20090830', '20090915' UNION ALL
SELECT	'sa', '20090801',       NULL, '20090815' UNION ALL
SELECT	'ayamas', '20090801', '20090830', '20090815' UNION ALL
SELECT	'ayamas', '20090801', '20090830', '20090915' UNION ALL
SELECT	'ayamas', '20090801',       NULL, '20090815'

-- Peso
SELECT	*
FROM	@Sample
WHERE	SaleDate BETWEEN StartDate AND COALESCE(EndDate, SaleDate)
	AND Name = 'sa'

-- Ayamas 1
SELECT	*
FROM	@Sample
WHERE	CASE
		WHEN EndDate IS NULL THEN	CASE
							WHEN SaleDate >= StartDate THEN 1
							ELSE 0
						END
		ELSE	CASE
				WHEN SaleDate BETWEEN StartDate AND EndDate THEN 1 
				ELSE 0
			END 
	END = 1
	AND Name = 'sa'

-- Ayamas 2
SELECT	*
FROM	@Sample
WHERE	CASE
		WHEN EndDate IS NULL AND SaleDate >= StartDate THEN 1 
		WHEN SaleDate BETWEEN StartDate AND EndDate THEN 1 
		ELSE 0
	END = 1
	AND Name = 'sa'

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

I am so sorry Peso.Your solutions works absolutely correct.It was my bad that I coould not point out the data correctly in my resultset using your solution.
Thank you very very much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 14:16:08

And now to the question, is there a speed difference?

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-30 : 10:20:54

quote:
Originally posted by Peso

And now to the question, is there a speed difference?

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

Sorry for soooooooo late reply.
Man it works like magic.
My statement used to take more than 14 minutes but with yours one, the query executes under a minute without any filters.So amazing.
I just replaced the case condition with urs in the where clause.My original query had about 8 tables joining each other plus 2 views on it.Didnt touch the structure,didnt add any index.But with just your change it worked like charm.
Thank you very very much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:02:14

From 14 minutes down to 1 minute?
That's agood start!

Thank you for the feedback.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources