SMS data and dates

I am looking to see what version of a program is installed across our network.  How can I select only the most recent data in SMS?  
My query:
SELECT
sys.name0 [Redtag],
sf.filename [File],
sf.fileversion [Vsn],
R.user_Name0 as LastUser,
r.creation_date0 as [Date]
FROM dbo.SoftwareFile AS sf
INNER JOIN dbo.SoftwareInventory AS si ON sf.FileId = si.FileId
INNER JOIN dbo.v_GS_System AS SYS on sys.resourceid = si.clientid
INNER JOIN dbo.v_R_System AS R on R.resourceid = si.clientid
where sf.filename = 'HSSTIMESTUDY.EXE'
order by sys.name0, r.creation_date0

I get this:
Redtag                       File                        Vsn                  LastUser      Date
ITSD-VM2-318648      HssTimeStudy.exe      1.02.0005      T0189      2010-02-20 12:11:10.000
ITSD-VM2-318648      HssTimeStudy.exe      1.02.0006      T0189      2010-03-05 14:08:25.000

I only want a single row returned for a given RedTag.  In the example I want the 2nd line since that appears to be later information.
IBMJunkmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
tigin44Commented:
SELECT
sys.name0 [Redtag],
sf.filename [File],
sf.fileversion [Vsn],
R.user_Name0 as LastUser,
r.creation_date0 as [Date]
FROM dbo.SoftwareFile AS sf
INNER JOIN dbo.SoftwareInventory AS si ON sf.FileId = si.FileId
INNER JOIN dbo.v_GS_System AS SYS on sys.resourceid = si.clientid
INNER JOIN dbo.v_R_System AS R on R.resourceid = si.clientid
where sf.filename = 'HSSTIMESTUDY.EXE'
and r.creation_date0 = (SELECT MAX(creation_date0 FROM dbo.v_R_System ))
order by sys.name0, r.creation_date0

0
 
IBMJunkmanAuthor Commented:
No go.  Get empty results.  SELECT MAX(creation_date0) FROM dbo.v_R_System return 1 record  2011-04-06 13:38:18.000
It appears there are no records in dbo.v_R_System with that date that match records in dbo.SoftwareInventory.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
IBMJunkmanAuthor Commented:
Gave up.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.