Using ISNULL

Question

Using ISNULL

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
November 6, 2011 at 10:48 am

#262332

Hi everybody
I would like to use the ISNULL function rather than COALESCE for the following problem:
I have data which has four date fileds: RequestDate_1, RequestDate_2, RequestDate_3 and SeenDate.
I want to calulate the DATEDIFF between the dates using this logic:
The difference between RequestDate_1 and SeenDate unless RequestDate_2 is populated in which case i want to calculate the difference between RequestDate_2 and SeenDate OR if RequestDate_3 is populated then the diference between RequestDate_3 and Seendate.
I seem to be getting myself tied up in knots and like i say i would prefer not to use COALESCE on this occassion...
Thanks in advance....

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Why do you not what to use Coalesce? From what you describe, it would be the way I'd go.

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

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 2

ByronOne

SSCarpal Tunnel

Points: 4656

November 6, 2011 at 10:56 am

#1403724

Hi

I simply want to learn the nested ISNULL variation

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

COALESCE(<expression1>,<expression2>,<expression3)

is equivalent to

ISNULL(ISNULL(<expression1>,<expression2>),<expression3>)

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

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 4

Thanks for your quick response.

I'm actually using it in a Where clause where the datediff is > 65 days but it doesn't seem to be quite working because i know there should be no records retuned but in fact i am gettig a few through - any ideas?

WHERE COALESCE (DATEDIFF(dd, RequestDate_1, SeenDate),

DATEDIFF(dd,RequestDate_2, SeenDate),

DATEDIFF(dd,RequestDate_3, SeenDate)) > 65

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

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 6

I'll have a play around and see what i can come up with..

Thanks for your help...

ryvlad SSC Enthusiast Points: 162 More actions · Answer 7

quick note here

if I understand your requirements

you want to calculate datedif() form either of the 3 fields

in order like so

if(field3 is not NULL) take DateDif () ignore the rest

if(field3 is NULL but field2 is not null)take DateDif () ignore the rest

if (field3 and field2 is null and field 1 is not )take DateDif ()

the code would be (using coalesce())

WHERE DATEDIF(COALESCE(RequestDate_3,RequestDate_2,RequestDate_1), SeenDate ) > 65

you need to start from the end to start in order of importance

EXAMPLE

/*

declare @RequestDate_3 datetime ,@RequestDate_2 datetime

,@RequestDate_1 datetime , @SeenDate datetime

set @SeenDate = '2011-10-31'

set @RequestDate_3 = null

set @RequestDate_2 = null

set @RequestDate_1 = '2011-06-01'

select DATEDIFF(dd,COALESCE(@RequestDate_3,@RequestDate_2,@RequestDate_1), @SeenDate )

set @SeenDate = '2011-10-31'

set @RequestDate_3 = null

set @RequestDate_2 = '2011-02-15'

set @RequestDate_1 = '2011-06-01'

select DATEDIFF(dd,COALESCE(@RequestDate_3,@RequestDate_2,@RequestDate_1), @SeenDate )

set @SeenDate = '2011-10-31'

set @RequestDate_3 = '2011-03-26'

set @RequestDate_2 = '2011-02-15'

set @RequestDate_1 = '2011-06-01'

select DATEDIFF(dd,COALESCE(@RequestDate_3,@RequestDate_2,@RequestDate_1), @SeenDate )

*/

ryvlad SSC Enthusiast Points: 162 More actions · Answer 8

GilaMonster (11/6/2011)
COALESCE(<expression1>,<expression2>,<expression3)
is equivalent to
ISNULL(ISNULL(<expression1>,<expression2>),<expression3>)

true but you have to still go from end to start like to get proper result 🙂

set @SeenDate = '2011-10-31'

set @RequestDate_3 = '2011-03-26'

set @RequestDate_2 = '2011-02-15'

set @RequestDate_1 = '2011-06-01'

select DATEDIFF(dd,ISNULL(ISNULL(@RequestDate_3,@RequestDate_2),@RequestDate_1), @SeenDate )