select VRS.Name0 [Computer Name], VRS.Creation_Date0 [Date], VRS.Operating_System_Name_and0 [Operating System], O.OU [OU Name], C.Instances AS [MachinesInOU]
FROM V_R_System VRS -- want to remove duplicates LEFT JOIN (Select ResourceID, MAX(System_OU_Name0) AS [OU] From v_RA_System_SystemOUName Group By ResourceID) O on O.ResourceID = VRS.ResourceID -- get a count of machines by OU LEFT JOIN (Select System_OU_Name0 AS [OU], Count(Distinct ResourceID) AS [Instances] From v_RA_System_SystemOUName Group By System_OU_Name0) AS C on C.OU = O.OU WHERE Operating_System_Name_and0 like '%10%' and DateDiff(dd,Creation_Date0, GetDate ()) <= 1 On Wed, Aug 31, 2016 at 10:04 PM, Boseman, Marcia H - Raleigh, NC < [email protected]> wrote: > 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 > > > > > >
