Need help with SQL query results

[almaler]

I am using the following SQL query (given to me by SQLBill and SQLDennis) to return computer names and a status of each record.

SELECT CSD.Name0, SMXLAD.Account00,
STATUS = CASE WHEN (SUBSTRING(SMXLAD.Account00, (PATINDEX('%NAME=%', SMXLAD.Account00)+6), (LEN(SMXLAD.Account00))))
NOT IN ('aacsecurity"', 'pcadmin"', 'aac desktopguys"')
THEN 'UNLOCKED'
ELSE 'LOCKED' END
FROM Computer_System_Data CSD
LEFT OUTER JOIN SMX_LOCAL_Admins_DATA SMXLAD
ON CSD.MachineID = SMXLAD.MachineID



The current output is this:

Computer Name Account00 Status
============ ======== =====
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="Security" Locked
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="desktop" Locked
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="admin" Locked
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="scott" Unlocked


I need for the output to show an "overall status" of Unlocked if any of the records for that computer name equals Unlocked else Locked. So for the example above, I want the output to look like this:


Computer Name Account00 Status Overall Status
============ ======== ===== ===========
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="Security" Locked Unlocked
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="desktop" Locked
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="admin" Locked
WSPAULSA \\WSPAULSA\root\cimv2:Win32_Group.Domain="NEO",Name="scott" Unlocked

Is this possible?