SQL 2012 - Need statement that can ignore rows with Zeros -

The following code gratefully rendered by Chaau gives me a look at the data value before and after each entry to find the spikes in data that are, in this case, invalid. And it works

Declare @StDate DateTime
	Declare @EndDate DateTime
	Set @StDate = '7/1/2015 00:00:00.000' ------ SET BEGIN DATE
	Set @EndDate = DateAdd(month,1,@StDate); -- One Month
	--SET @EndDate =  DateAdd(day,1,@StDate); --One Day

with X as -- "X" becomes a "table" that will be referenced in subsequent code. 
	(select dateadd(month,(datediff(month,0,TIMEandDATE)),0) as ReadingDate, TimeandDate,Value, id, tagid, _COMMENT,
	Lag(value) over( partition by tagid order by id) preValue,
	Lead(value) over(partition by tagid order by id) postValue
	 from JunkTesting
		Where TagID = 3004
		and TimeAndDate >= @StDate
		and TimeAndDate < @EndDate
	)
	
-------------------------------------------------------------------------------------------------------------------------------
--Now, locate the values that are WAY larger (.7 times greater here) than the average between pre and post value: 
-------------------------------------------------------------------------------------------------------------------------------

select * from X
	where ((preValue is NOT NULL) OR (preValue <> 0)) and ((postValue IS NOT NULL) OR (PostValue <> 0))
	AND ((preValue + postValue) / 2.0) * 1.7 < value;

Open in new window



Gives this Result (Apologies for the formatting, couldn't get it any better formatted):
2015-07-01 00:00:00.000      2015-07-01 11:00:00.163      0.1835148      8445239      3004      NULL      0.05925107      0.0586946 <<<
2015-07-01 00:00:00.000      2015-07-03 17:00:00.327      0.06597149      8462789      3004      NULL      0.06490141      0
2015-07-01 00:00:00.000      2015-07-03 23:05:00.527      0.08442062      8464760      3004      NULL      0                      0.0918687
2015-07-01 00:00:00.000      2015-07-06 20:55:00.193      0.1163533      8487467      3004      NULL      0.05706799      0.06995243 <<<
2015-07-01 00:00:00.000      2015-07-08 00:10:00.533      0.1219608      8496350      3004      NULL      0.04983395      0.0506044 <<<
2015-07-01 00:00:00.000      2015-07-09 03:20:00.677      0.09957367      8505179      3004      NULL      0.05223101      0.05180299<<<
2015-07-01 00:00:00.000      2015-07-10 06:20:00.403      0.04431206      8513954      3004      NULL      0.04474008      0
2015-07-01 00:00:00.000      2015-07-10 18:40:00.970      0.09649169      8517950      3004      NULL      0                      0.09024215
2015-07-01 00:00:00.000      2015-07-10 22:05:00.220      0.04816455      8519057      3004      NULL      0.04825014      0
2015-07-01 00:00:00.000      2015-07-11 03:45:00.967      0.07042325      8520920      3004      NULL      0                      0.0595507
2015-07-01 00:00:00.000      2015-07-14 16:40:00.973      0.05248785      8548514      3004      NULL      0.05248785      0
2015-07-01 00:00:00.000      2015-07-15 00:40:00.947      0.08142424      8551133      3004      NULL      0                      0.07170743
2015-07-01 00:00:00.000      2015-07-17 12:55:00.210      0.0462383      8570708      3004      NULL      0.04666632      0
2015-07-01 00:00:00.000      2015-07-17 18:45:00.720      0.08001167      8572598      3004      NULL      0                      0.06528664
2015-07-01 00:00:00.000      2015-07-21 09:20:00.057      0.04461169      8600759      3004      NULL      0.0445689      0
2015-07-01 00:00:00.000      2015-07-21 11:54:59.987      0.06858265      8601596      3004      NULL      0                      0.07204986
2015-07-01 00:00:00.000      2015-07-24 17:30:00.473      0.05509895      8626814      3004      NULL      0.05625474      0
2015-07-01 00:00:00.000      2015-07-25 00:05:00.767      0.08805907      8628974      3004      NULL      0                      0.08360732
2015-07-01 00:00:00.000      2015-07-28 16:35:00.317      0.04465449      8657729      3004      NULL      0.04392678      0
2015-07-01 00:00:00.000      2015-07-28 21:05:00.487      0.06960994      8659187      3004      NULL      0                      0.06494421
2015-07-01 00:00:00.000      2015-07-31 21:45:00.037      0.05218822      8682812      3004      NULL      0.05312991      0


The remaining problem I have been reluctant to ask about and therefore stuck on, is that if the preValue or postValue is a 0,  I don't want to list the row (and in subsequent code change the value) .  

Instances above where I WOULD want the rows listed are marked with "<<<".

I have tried all the "OR" syntax I can think of  (my last attempt remains in the code)  and can't come up with anything that prevents displaying the rows that have a Zero in the preValue OR postValue fields.  The code finds NULLS . . . why doesn't it find the Zeros too?
LVL 1
Salad-DodgerInstrumentationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
I think replacing the last select with the following should give you what you're looking for:

select 
	* 
from 
	X
where 
	coalesce( preValue, 0 ) <> 0 and
	coalesce( postValue, 0 ) <> 0 and
	( (preValue + postValue ) / 2.0 ) * 1.7 < value;

Open in new window


You can use isnull( ) instead of coalesce and it will give you slightly better performance, but I like sticking with ANSI SQL as much as possible.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Salad-DodgerInstrumentationAuthor Commented:
Well now... that looks like it works!
Online explanations of IS NULL vs. Coalesce are sparse, but there is big difference between them isnt there.
I would not have gotten there on my own. Thank you
PortletPaulEE Topic AdvisorCommented:
Not sure why you need either isnull() or coalesce()

select
*
from x
where prevalue <> 0 and postvalue <> 0

If you compare to a value (in this case not equal to zero) then NULL (which has no value) cannot be returned and neither can zero because you have asked for not equal to zero.

ISNULL()
COALESCE()

& here's a reference to differences between them
http://sqlmag.com/t-sql/coalesce-vs-isnull

ISNULL is specific to T-SQL
COALESCE is ANSI standard hence more broadly supported
they are not identical
PortletPaulEE Topic AdvisorCommented:
Oh, and be careful with the space (my fault, not yours)

ISNULL()
IS NULL   --<< with a space it's not a function

case when ISNULL(col,0) = 0 then

case when col IS NULL then

IS NULL (with space) is ANSI standard and very broadly supported
Salad-DodgerInstrumentationAuthor Commented:
Thank you Very Much. This was extremely helpful to me and just obliterated a nagging, laborious task I didn't look forward to each month. Its awesome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.