Conditional WHERE clause in TSQL

Question

Post reply

Conditional WHERE clause in TSQL

4EverandEver

Default port

Points: 1485
More actions
March 9, 2010 at 12:28 am

#221405

I want to return records with a datefiled older that 30 days. but it could be any of two datefiled that i need to check if its older than 30 days. My WHERE clause needs to check if DateField2 is not null then use otherwise use DateField1
How can achieve something like this in TSQL:
Select Count(*)
FROM CourtHearing
WHERE (if postponement_date is not null
then DATEDIFF(dd, postponement_date, GETDATE()) > 30
else DATEDIFF(dd, Hearing_date, GETDATE()) > 30
Many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Select Count(*)

FROM CourtHearing

WHERE

CASE WHEN postponement_date is not null

THEN DATEDIFF(dd, postponement_date, GETDATE())

ELSE DATEDIFF(dd, Hearing_date, GETDATE())

END > 30

It won't perform well, that's an index or table scan at best.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 2

How about this

Select Count(*)

FROM CourtHearing

WHERE datediff(dd,isnull(postponement_date,Hearing_date),getdate()) > 30

This one will not perform well either.

Regards,
Raj

http://Strictlysql.blogspot.com

4EverandEver Default port Points: 1485 More actions · Answer 3

4EverandEver

Default port

Points: 1485

March 9, 2010 at 1:15 am

#1130491

thanks this worked.

4EverandEver Default port Points: 1485 More actions · Answer 4

Thanks for the replies. you both mentioned that these solutions will not perform well. IS that terms terms of speed or will it cause any harm to the the DB?

Regards

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

4EverandEver (3/9/2010)
Thanks for the replies. you both mentioned that these solutions will not perform well. IS that terms terms of speed or will it cause any harm to the the DB?

It is in terms of speed as the resulting plan of these queries would be a table scan or an index scan

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Paul White SSC Guru Points: 150467 More actions · Answer 6

The query will perform well if written correctly 😉

Plan produced from the following test rig and query:

--

-- Test table

--

DROP TABLE #CourtHearing;

GO

CREATE TABLE #CourtHearing

(

hearing_id INTEGER IDENTITY PRIMARY KEY,

padding CHAR(127) NOT NULL DEFAULT (''),

hearing_date DATETIME NOT NULL,

postponement_date DATETIME NULL,

);

GO

--

-- Add 50,000 rows

--

WITH Numbers (n)

AS (

SELECT TOP (50000)

n = ROW_NUMBER()

OVER(ORDER BY (SELECT 0))

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3

ORDER BY

n ASC

)

INSERT #CourtHearing

(hearing_date)

SELECT -- Random date in the last 90 days

DATEADD(HOUR, 0 - RAND(CHECKSUM(NEWID())) * 90 + 24, CURRENT_TIMESTAMP)

FROM Numbers;

GO

--

-- 1 in 5 hearings postponed for 28 days

--

UPDATE #CourtHearing

SET postponement_date = DATEADD(DAY, 28, hearing_date)

FROM #CourtHearing CH

WHERE (hearing_id % 5) = 0;

GO

-- Useful index

CREATE INDEX nc1 ON #CourtHearing (postponement_date, hearing_date);

GO

--

-- Query

--

WITH Postponed

AS (

-- Get the primary key for

-- every hearing that was postponed

-- Postponed hearing took place in the last 30 days

SELECT hearing_id

FROM #CourtHearing

WHERE postponement_date IS NOT NULL

AND postponement_date >= DATEADD(DAY, -30, CURRENT_TIMESTAMP)

AND postponement_date < CURRENT_TIMESTAMP

),

NotPostponed

AS (

-- Get the primary key for

-- every hearing that was NOT postponed

-- Hearing occurred in the last 30 days

SELECT hearing_id

FROM #CourtHearing

WHERE postponement_date IS NULL

AND hearing_date >= DATEADD(DAY, -30, CURRENT_TIMESTAMP)

AND hearing_date < CURRENT_TIMESTAMP

),

CombinedSets

AS (

-- Join the two sets

-- UNION ALL is safe since hearing_id

-- is a unique, and a hearing was either

-- postponed or it wasn't

SELECT hearing_id

FROM Postponed

UNION ALL

SELECT hearing_id

FROM NotPostponed

)

SELECT COUNT_BIG(*)

FROM CombinedSets;

GO

DROP TABLE #CourtHearing;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi