SimonJohnG
asked on
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.ComputerPr
SELECT
ESEventlogComputer.eventco
,ESLogonTracking.start_dat
,ESLogonTracking.duration
,ESEventlogGroup.groupname
,ESEventlogUser.eventuser
,ESLogonTracking.IsAdmin
,ESLogonTracking.LogonID
,ESLogonTracking.ComputerP
,ESIPAddress.name
,ESLogonTracking.LogonType
FROM
ESLogonTracking
INNER JOIN ESEventlogComputer
ON ESLogonTracking.computerna
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_date
AND
ESLogonTracking.ComputerPr
AND
ESLogonTracking.duration >0
AND
ESLogonTracking.IsAdmin = (@Admin)
ORDER BY ESLogonTracking.start_date
SELECT
ESEventlogComputer.eventco
,ESLogonTracking.start_dat
,ESLogonTracking.duration
,ESEventlogGroup.groupname
,ESEventlogUser.eventuser
,ESLogonTracking.IsAdmin
,ESLogonTracking.LogonID
,ESLogonTracking.ComputerP
,ESIPAddress.name
,ESLogonTracking.LogonType
FROM
ESLogonTracking
INNER JOIN ESEventlogComputer
ON ESLogonTracking.computerna
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_date
AND
ESLogonTracking.ComputerPr
AND
ESLogonTracking.duration >0
AND
ESLogonTracking.IsAdmin = (@Admin)
ORDER BY ESLogonTracking.start_date
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Just out of curiosity, which part of my first comment did not directly address your question?
ASKER
Sorry, someone elase closed this without my permission, I will update
I agree that we should equally split the points. Both answers (mine and jim's) are good.
SELECT blah, blah, blah
FROM YourStuff
WHERE (COALESCE(ESLogonTracking.
AND { other where stuff here}