Problem returning correct rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem returning correct rows

Author

Topic

egglestc
Starting Member

3 Posts

Posted - 2010-05-25 : 16:06:51

I am trying to make a statement that returns the number of machines that hasn't been logged on to in the last 37 days for housekeeping purposes.

Currently, I can get it to display all machines, but can not get it to return only the ones inactive for 37 days. Here is the statement so far:

SELECT V_R_system.name0 AS 'System Name',
v_r_system.User_Name0 AS 'Last Logged On User',
MIN(datediff(day,Lastconsoleuse0,getdate())) AS 'Days Since Last Reported Use'

FROM v_gs_system_console_user

LEFT JOIN V_r_system ON v_r_system.resourceid=v_gs_system_console_user.resourceID
LEFT JOIN V_Ra_system_systemOUName ON V_ra_system_systemOUName.resourceid = V_R_system.resourceid

WHERE system_OU_name0 like 'VDI'
GROUP BY v_r_system.name0,v_r_system.User_Name0

This returns:

Administrator---77
BollaerC--------5
bonninf---------55
DeprincN--------5
dermauts--------5
DigiacoV--------13
eeckmanc--------4
EveraerA--------0
LinthouF--------4
NelisJ --------6
RottierP--------5
vandepub--------6
vanhillk--------5
ZadelhoF--------2

As you can see, there are 2 machines that fit the criteria: one with 77 inactive days, the other with 55.

I am having problems with getting the datediff to only return the ones with 37 or greater days inactive.

If I add the line: AND datediff(day,Lastconsoleuse0,getdate()) >= 37, I get the following results:

Administrator----77
bonninf ---------55
DigiacoV---------67
ZadelhoF---------43

It correctly shows the 2 rows I need, but changes the value for the DigiacoV and ZadelhoF entries. Any help would be greatly appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-25 : 17:09:54

Because you have

MIN(datediff(day,Lastconsoleuse0,getdate()))

in the SELECT and just

datediff(day,Lastconsoleuse0,getdate()) >= 37

as your condition.

Do you need the MIN()?

egglestc
Starting Member

3 Posts

Posted - 2010-05-26 : 08:13:30

The initial query came from a reporting tool that automatically pulls everybody in the list, and I am trying to modify to only show inactive machines. I can't get it to work without the MIN

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 11:11:46

Wht does this give you?

SELECT V_R_system.name0 AS 'System Name',
v_r_system.User_Name0 AS 'Last Logged On User', 
datediff(day,Lastconsoleuse0,getdate()) AS 'Days Since Last Reported Use'
FROM v_gs_system_console_user
LEFT JOIN V_r_system ON v_r_system.resourceid=v_gs_system_console_user.resourceID
LEFT JOIN V_Ra_system_systemOUName ON V_ra_system_systemOUName.resourceid = V_R_system.resourceid
WHERE system_OU_name0 like 'VDI' 
and datediff(day,Lastconsoleuse0,getdate()) >= 37
GROUP BY v_r_system.name0,v_r_system.User_Name0

And this?

select * from 
(
SELECT V_R_system.name0 AS 'System Name',
v_r_system.User_Name0 AS 'Last Logged On User', 
MIN(datediff(day,Lastconsoleuse0,getdate())) AS 'Days Since Last Reported Use'
FROM v_gs_system_console_user
LEFT JOIN V_r_system ON v_r_system.resourceid=v_gs_system_console_user.resourceID
LEFT JOIN V_Ra_system_systemOUName ON V_ra_system_systemOUName.resourceid = V_R_system.resourceid
WHERE system_OU_name0 like 'VDI' 
GROUP BY v_r_system.name0,v_r_system.User_Name0
) t
where t.[Days Since Last Reported Use] >= 37

egglestc
Starting Member

3 Posts

Posted - 2010-05-26 : 11:18:42

I just figured it out... I found out that I cant use a function in a where clause, I had to use having. Thanks for the suggestions!

SELECT V_R_system.name0 AS 'System Name',
v_r_system.User_Name0 AS 'Last Logged On User',
MIN(datediff(day,Lastconsoleuse0,getdate())) AS 'Days Since Last Reported Use'

FROM v_gs_system_console_user

LEFT JOIN V_r_system ON v_r_system.resourceid=v_gs_system_console_user.resourceID
LEFT JOIN V_Ra_system_systemOUName ON V_ra_system_systemOUName.resourceid = V_R_system.resourceid

WHERE system_OU_name0 like 'VDI'

GROUP BY v_r_system.name0,v_r_system.User_Name0
HAVING MIN(datediff(day,Lastconsoleuse0,getdate())) >= 37
ORDER BY MIN(datediff(day,Lastconsoleuse0,getdate())) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources