Filter out Zero but keep NULL

I have a query in SQL server report builder that checks ESLogonTracking.duration and includes records in a report if the field is greater than 0. What I would like is the query to return records that are greater than 0 and any that show a NULL duration but not at the expense of my other citeria. If I use ESLogonTracking.duration >0 or ESLogonTracking.duration IS NULL this breaks ESLogonTracking.ComputerProductType IN (@Type) . Can AND's be hierarchical or is there a better way

SELECT
  ESEventlogComputer.eventcomputer
  ,ESLogonTracking.start_datetime
  ,ESLogonTracking.duration
  ,ESEventlogGroup.groupname
  ,ESEventlogUser.eventuser
  ,ESLogonTracking.IsAdmin
  ,ESLogonTracking.LogonID
  ,ESLogonTracking.ComputerProductType
  ,ESIPAddress.name
  ,ESLogonTracking.LogonType
FROM
  ESLogonTracking
  INNER JOIN ESEventlogComputer
    ON ESLogonTracking.computername = ESEventlogComputer.id
  INNER JOIN ESEventlogUser
    ON ESLogonTracking.username = ESEventlogUser.id
  INNER JOIN ESIPAddress
    ON ESLogonTracking.SourceIP = ESIPAddress.id
  INNER JOIN ESEventlogGroup
    ON ESLogonTracking.groupname = ESEventlogGroup.id
WHERE
  ESLogonTracking.start_datetime >= @StartDate AND ESLogonTracking.start_datetime < dateadd(day,1,@EndDate)
AND
ESLogonTracking.ComputerProductType IN (@Type)
AND
ESLogonTracking.duration >0
AND
ESLogonTracking.IsAdmin = (@Admin)
ORDER BY ESLogonTracking.start_datetime DESC
SimonJohnGAsked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What I would like is the query to return records that are greater than 0 and any that show a NULL duration but not at the expense of my other citeria

SELECT blah, blah, blah
FROM YourStuff
WHERE (COALESCE(ESLogonTracking.duration,1) > 0  )
   AND { other where stuff here}
0
ArgentiCommented:
Can you try

WHERE
(ESLogonTracking.start_datetime >= @StartDate)
AND
(ESLogonTracking.start_datetime < dateadd(day,1,@EndDate))
AND
(ESLogonTracking.ComputerProductType IN (@Type))
AND
((ESLogonTracking.duration >0) OR (ESLogonTracking.duration is NULL))
AND
(ESLogonTracking.IsAdmin = (@Admin))
ORDER BY
ESLogonTracking.start_datetime DESC
0

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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw I cleaned up your T-SQL a little.
Used aliases so long table names do not have to be repeated
Intented
JOIN instead of INNER JOIN
SELECT
	lc.eventcomputer
	,lt.start_datetime
	,lt.duration
	,lg.groupname
	,lu.eventuser
	,lt.IsAdmin
	,lt.LogonID
	,lt.ComputerProductType
	,ipa.name
	,lt.LogonType
FROM ESLogonTracking lt
	JOIN ESEventlogComputer lc ON lt.computername = lc.id
	JOIN ESEventlogUser lu ON lt.username = lu.id
	JOIN ESIPAddress ipa ON lt.SourceIP = ipa.id
	JOIN ESEventlogGroup lg ON lt.groupname = lg.id
WHERE
	lt.start_datetime >= @StartDate AND lt.start_datetime < dateadd(day,1,@EndDate)
	AND lt.ComputerProductType IN (@Type)
	AND lt.duration >0 
	AND lt.IsAdmin = (@Admin)
ORDER BY lt.start_datetime DESC 

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just out of curiosity, which part of my first comment did not directly address your question?
0
SimonJohnGAuthor Commented:
Sorry, someone elase closed this without my permission, I will update
0
ArgentiCommented:
I agree that we should equally split the points. Both answers (mine and jim's) are good.
0
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.