T-SQL Query Help - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL Query Help

Author

Topic

dimepop
Starting Member

33 Posts

Posted - 2012-07-12 : 08:18:01

Hi,
Can i get some help with a query?
Having the below database
Which query will give me the % of Computers in Computername grouped by the first 4 letters (where they give me the site location) and where TimetoLogon over 60 and WindowsVersion Any of the XP versions?( SP1, SP2, SP3)

ComputerName TimeToLogon WindowsVersion
----------- ------------ ----------------
sfc1-080 43 Microsoft Windows XP Professional Service Pack 3
nyc1-188 12 Microsoft Windows 7 Enterprise Service Pack 1
lan1-323 16 Microsoft Windows XP Professional Service Pack 3
lan2-190 11 Microsoft Windows 7 Enterprise Service Pack 1
lan3-190 16 Microsoft Windows XP Professional Service Pack 3
hou1-559 98 Microsoft Windows XP Professional Service Pack 3
sea1-020 38 Microsoft Windows XP Professional Service Pack 3

I have this query that gets the % of Computers starting by lan with timetologon over 60 by now i would like to include all sites and include only the XP versions.

"SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1, COUNT(CASE WHEN TimeToLogon > 60 THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%Over60] FROM BootLog WHERE (ComputerName LIKE 'lan%') GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)"

So at the end i will end up with a result like for windows XP machines:
Site %Timetologonover60
sfc1 12
lan1 11
lan2 12
hou1 12

Thanks

dimepop
Starting Member

33 Posts

Posted - 2012-07-12 : 08:21:10

I forgot to include something....
There is another row called DateAndTime with values like:
2012-03-14 09:37:12.000
2012-03-14 08:37:59.000
2012-03-14 08:38:30.000
2012-03-14 08:39:41.000
2012-03-14 08:39:56.000
2012-03-14 08:40:34.000
2012-03-14 08:41:25.000

the report ideally will only look at the last 30 days.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 09:53:09

[code]
SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1,
COUNT(CASE WHEN TimeToLogon > 60 THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%Over60]
FROM BootLog
WHERE WindowsVersion LIKE 'Windows XP%'
and DateAndTime >= DATEADD(DD,dATEDIFF(dd,0,GETDATE())-30,0)
GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-12 : 11:46:41

Hi Visakh, thanks for the query.
It runs perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 17:24:41

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-13 : 07:57:58

Hi Visakh16
Which query would display the following?
I would like to add the second row so i can display both in a combochart
Thanks

Site %XPTimetoLogonOver60 %Win7TimetoLogonOver60
sfc1 12 22
lan1 11 21
lan2 12 22
hou1 12 22

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:35:34

[code]
SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1,
COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows XP%' THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%XPOver60],
COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows 7%' THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%Win7Over60]
FROM BootLog
WHERE (WindowsVersion LIKE 'Windows XP%' OR WindowsVersion LIKE 'Windows 7%')
and DateAndTime >= DATEADD(DD,dATEDIFF(dd,0,GETDATE())-30,0)
GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-13 : 11:41:51

Thanks, I Owe you a drink!
:)

dimepop
Starting Member

33 Posts

Posted - 2012-07-16 : 07:33:09

Hi Visakh, sorry to bother you again, it seems that the last query doesn't work as expected.
The query returns the correct % of Win7 machines, but the incorrect % of XP machines.
If i run the query just to get the % of xp machines i get a different result as in the combined query.
Can you help?
Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 10:00:23

explain with sample data what issue you're facing. I cant make out exact issue without seeing sample output.

my only guess now is probably you're after this??


SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1,
 COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows XP%' THEN 1 END) * 100.0 / NULLIF (COUNT(CASE WHEN WindowsVersion LIKE 'Windows XP%' THEN 1 END), 0) AS [%XPOver60],
 COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows 7%' THEN 1 END) * 100.0 / NULLIF (COUNT(CASE WHEN WindowsVersion LIKE 'Windows 7%' THEN 1 END), 0) AS [%Win7Over60]  
FROM BootLog 
WHERE  (WindowsVersion LIKE 'Windows XP%' OR  WindowsVersion LIKE 'Windows 7%')
and DateAndTime >= DATEADD(DD,dATEDIFF(dd,0,GETDATE())-30,0)
GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dimepop
Starting Member

33 Posts

Posted - 2012-07-16 : 11:03:58

Hi Visakh, now it works fine, but before the results for XP were wrong.
I noticed it because if i run the query to get the results for just XP then it gave me different results.
No the combines query shows the same results as the individual queries.
Many Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 11:42:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources