1

I'm trying to write a sql query to only display the software sorted on the last used date. Currently its showing all dates the software was used on a particular machine. Please could some let me know how i could just show the newest date. Thanks David

select DISTINCT  
SYS.Name0 as 'Computer', 
Usage.CompanyName0 as 'Publisher',
Usage.ProductName0 As 'Software Name',
Usage.ProductVersion0 AS 'Version',
Console.TopConsoleUser0 as 'User',
------abs(DATEDIFF(day,getdate(),Max_LastUsed)) as 'Days Since Used'
Max_LastUsed as 'Max_LastUsed'

FROM 
  dbo.v_R_System As SYS
  LEFT OUTER JOIN  v_GS_System_Console_Usage console on  sys.ResourceID = console.ResourceID 
LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID
LEFT OUTER JOIN (SELECT MAX(Usage.LastUsedTime0) as 'Max_LastUsed', usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0
    FROM dbo.v_R_System As SYS
         LEFT OUTER JOIN  v_GS_System_Console_Usage console on  sys.ResourceID = console.ResourceID 
        LEFT OUTER JOIN v_GS_CCM_RECENTLY_USED_APPS Usage on sys.ResourceID = Usage.ResourceID
    WHERE usage.ProductName0 LIKE '%indesign%' or usage.ProductName0 like '%Incopy%' or usage.ProductName0 like '%Photoshop%'
    group by usage.ResourceID, usage.ProductName0, usage.ProductVersion0, usage.CompanyName0) max_usage on max_usage.ResourceID = usage.ResourceID

WHERE 
  usage.ProductName0 LIKE '%indesign%' or usage.ProductName0 like '%Incopy%' or usage.ProductName0 like '%Photoshop%'
  and Name0 like '%%'
David Newman
  • 19
  • 2
  • Make it easy to assist you: [mcve]. – jarlh Dec 09 '19 at 12:42
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Dec 09 '19 at 12:48
  • The system Center Configuration Manager database sits on SQL Server 2016: Standard, Enterprise. Hope this helps – David Newman Dec 09 '19 at 14:07
  • Your logic appears to be (and this is just a guess) faulty. Your derived table groups by multiple columns yet you join to it based on a single column. That is likely incorrect. Usage of distinct is also an indication of faulty join logic. Maybe this is as simple as grouping by ResourceID? – SMor Dec 09 '19 at 14:45
  • maybe have a look here to find different valid approaches: https://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate – Marcel Jan 07 '20 at 15:32

0 Answers0