missing the counts per ou. :)
On Thu, Sep 1, 2016 at 10:31 AM, Garth Jones <ga...@enhansoft.com> wrote: > 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/> > > [image: Description: Description: cid:image001.jpg@01CC63FB.974F4EC0] > <http://www.enhansoft.com/> > > *Enhancing Your Business* > > [image: Description: Description: Description: Description: Description: > Description: Description: cid:image004.png@01CD5E19.F3C3A110] > <http://www.enhansoft.com/blog>[image: Description: Description: > Description: Description: Description: Description: Description: > cid:image005.png@01CD5E19.F3C3A110] <https://twitter.com/enhansoft>[image: > Description: Description: Description: Description: Description: > Description: Description: cid:image006.jpg@01CD5E19.F3C3A110] > <http://www.facebook.com/EnhansoftInc>[image: 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>[image: Description: > Description: Description: Description: Description: Description: > Description: cid:image007.png@01CD5E19.F3C3A110] > <http://myitforum.com/myitforumwp/community/groups/enhansoft/> > > > > *Subscribe to Enhansoft’s Newsletter! <http://www.enhansoft.com/register>* > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com] *On Behalf Of *Boseman, Marcia H - Raleigh, NC > *Sent:* Wednesday, August 31, 2016 11:04 PM > *To:* mssms@lists.myitforum.com > *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 > > > > > > > >