CODEWITHSUNDEEP
sqlsccmsccm-2007
Adryanno Tussing
Adryanno Tussing

Reputation: 1

How to do a query with unique results between two tables?

I need to do a query to get the last accessed file per user, in SCCM 2012. I'm trying to make a query in sql but i'm getting a lot of duplicate results.

The result that I need must contain only the last date (most recently) for each user.

There is the query that i'm using:

SELECT     
  dbo.v_GS_SoftwareFile.FileName, 
  dbo.v_R_System.User_Name0, 
  dbo.v_GS_SoftwareFile.FileModifiedDate    
FROM 
  dbo.v_GS_SoftwareFile 
  CROSS JOIN dbo.v_R_System    
WHERE     
  (dbo.v_GS_SoftwareFile.FileName = N'outlook.exe') 
  AND (dbo.v_GS_SoftwareFile.FileModifiedDate > CONVERT(DATETIME, '2015-02-01 00:00:00', 102))    
GROUP BY 
  dbo.v_R_System.User_Name0, 
  dbo.v_GS_SoftwareFile.FileName, 
  dbo.v_GS_SoftwareFile.FileModifiedDate

What I need to add to this query?

Upvotes: 0

Views: 461

Answers (1)

AHiggins
AHiggins

Reputation: 7219

Your CROSS JOIN might be responsible for the 'duplicate results' you report, since you don't have an actual join condition there (so, if you have 10 records in one table, and 100 records in another, you will have 10x100=1000 records). Is there a common key between your SoftwareFile and System tables?

Once you've added the JOIN condition, to get it down to a single date per user, use the MAX() function as follows:

SELECT     
      dbo.v_GS_SoftwareFile.FileName, 
      dbo.v_R_System.User_Name0, 
      MAX(dbo.v_GS_SoftwareFile.FileModifiedDate) AS LastFileModifiedDate    
FROM 
      dbo.v_GS_SoftwareFile 
       CROSS JOIN 
      dbo.v_R_System    
WHERE     
    (dbo.v_GS_SoftwareFile.FileName = N'outlook.exe') 
    AND (dbo.v_GS_SoftwareFile.FileModifiedDate > CONVERT(DATETIME, '2015-02-01 00:00:00', 102))    
GROUP BY 
      dbo.v_R_System.User_Name0, 
      dbo.v_GS_SoftwareFile.FileName

Upvotes: 1

Related Questions