Try this.,
select
DS.Name0 as'Computer Name',
DS.Creation_Date0 as 'Date',
DS.Operating_System_Name_and0 as 'Operating System',
OS.Caption0,
max(OU.System_OU_Name0) as 'OU Name'
FROM
dbo.v_R_System DS
Inner Join dbo.v_RA_System_SystemOUName OU on OU.ResourceID =
DS.ResourceID
left outer join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = DS.ResourceID
*WHERE
DS.Operating_System_Name_and0 like '%10%'
and DateDiff(dd,DS.Creation_Date0, GetDate ()) <= 1
Group BY
DS.Name0,
DS.Creation_Date0,
DS.Operating_System_Name_and0,
OS.Caption0,
OU.System_OU_Name0
Order BY
DS.Name0,
DS.Creation_Date0,
DS.Operating_System_Name_and0,
OS.Caption0,
OU.System_OU_Name0
Garth Jones
Chief Architect
www.Enhansoft.com<http://www.enhansoft.com/>
[Description: Description:
cid:[email protected]]<http://www.enhansoft.com/>
Enhancing Your Business
[Description: Description: Description: Description: Description: Description:
Description:
cid:[email protected]]<http://www.enhansoft.com/blog>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<https://twitter.com/enhansoft>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<http://www.facebook.com/EnhansoftInc>[Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Enhansoft's YouTube
Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description:
Description: Description: Description: Description: Description:
cid:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/>
Subscribe to Enhansoft's Newsletter!<http://www.enhansoft.com/register>
From: [email protected] [mailto:[email protected]] On
Behalf Of Boseman, Marcia H - Raleigh, NC
Sent: Wednesday, August 31, 2016 11:04 PM
To: [email protected]
Subject: [mssms] SQL Query Help with total and count
I am running the query below and want to remove duplicates and then get a count
of machines by OU. Any help would be appreciated
select DS.Name0 [Computer Name],
DS.Creation_Date0 [Date], DS.Operating_System_Name_and0 [Operating System],
OU.System_OU_Name0 [OU Name]
FROM
dbo.System_DISC DS
Inner Join dbo.System_System_OU_Name_ARR OU on OU.ItemKey = DS.ItemKey
WHERE Operating_System_Name_and0 like '%10%' and
DateDiff(dd,Creation_Date0, GetDate ()) <= 1
Group BY Name0, Creation_Date0, Operating_System_Name_and0, System_OU_Name0
