Using a 'created' field name in a "WHERE" statement

[PWD]

Good afternoon. Is it possible to use a 'created' field name in a "WHERE" statement?

Some of our tables contain entries where the record was opened (presumably in eror) and closed at the same time so when we calculate the difference the result is 0. What I thought might be useful would be to eclude the records at selection time. Here is what I mean:

Case
When Datediff(day,epstart,EpEnd)/365.25 is Null
Then Datediff(day,epstart,getdate())/365.25
Else Datediff(day,epstart,EpEnd)/365.25
End As StayYears

WHERE StayYears <> 0

Many thanks,
DÇ$

 

[Olaf Doschke]

First of all Null is not 0, a datediff of two equal values will not be Null, unless one of the datetime fields is Null.
Then you will not be able to use the created name in WHERE, no, unfortunately not.
But you can simplify the expression and repeating it in the WHERE clause will not insult the eye as much.

Datediff(day,epstart,COALESCE(EpEnd,getdate())/365.25 As StayYears

Bye, Olaf.

 

[PWD]

Hi Olaf, thanks for the response. Some of the episodes don't have an end date recorded and that produces a NULL.

I'll have to have a look at the COALESCE feature.

The ones that were opened and immediately closed are the ones that produced the 0 (actually 0.000000).

Yes, I'd figured that I'd have to use the same expression in the WHERE statement.

Many thanks,
DÇ$

 

[Olaf Doschke]

If you need a link:

https://msdn.microsoft.com/de-de/library/ms190349.aspx

ISNULL(EpEnd,getdate()) works quite similar and would also be sufficient in this case, as you only need one alternative value getdate() and know that replacement is never NULL. Read the paragraph about the differences in the COALESCE online help topic.

Bye, Olaf.

 

[PWD]

OK, will do.

Many thanks.

Many thanks,
DÇ$