SQL query problem - getting latest results per asset

Talon69
Talon69 used Ask the Experts™
on
Running an SQL Asset Tracking database. We have a table with all of our assets listed and a table that contains the Portable appliance data. They are linked via a third table that contains details of each task.

What I want to generate is a report that shows the latest test data for each asset.

For example, the 3 tables are called Asset, PAT and PPM. One asset can have many PAT records linked via a foreign key in the PAT table to the primary key of the PPM table. The PPM table has a foreign key to the primary key of the Asset table.
I would like to select the asset ID from the asset table and the date last tested from the PAT table.

Could some SQL guru please offer some guidance???

Many thanks in advance!

W
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
Without knowing the structure and sample data it is difficult to provide any guidance.
But normally when we call "latest", it is either based on some date/time field or based on some incremented number generated.

Please provide us the Structure of 3 tables and some sample records.

The query that you want should not be difficult one.

Author

Commented:
Hi,

Thanks for the reply.

Sample data:

Asset table :
Ass_Pri    AssetID   Asset_desc
001         12345A   Monitor
002         12345B   PC
003         12345C   Printer

PAT table:
PAT_pri      TestDate    Pass/Fail     PPM_pri_seq
101            01/01/10         P           5000
102            01/01/10         P           5001
103            01/01/10         P           5002
104            01/01/11         P           5003
105            01/01/11         P           5004
106            01/01/11         P           5005
107            01/11/12         P           5006
108            01/11/12         P           5007
109            01/11/12         P           5008


PPM table:
PPM_pri    PPM_desc     Ass_pri_seq
5000         PAT test        001
5001         PAT test        002
5002         PAT test        003
5003         PAT test        001
5004         PAT test        002
5005         PAT test        003
5006         PAT test        001
5007         PAT test        002
5008         PAT test        003


This is the very basic data. When I mean latest, it relates to both the latest in date i.e. closest to now and also it could mean the highest PAT_pri (PAT table primary key) per asset.

Hope this helps

Author

Commented:
Forgot to add:

The data I would hope to extract would be:

AssetID     TestDate    Pass/Fail
12345A     01/11/12        P
12345B     01/11/12        P
12345C     01/11/12        P

And not to see previous results.

HTH
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Hi,
I hope the below query should provide you the desired result:
SELECT a.AssetID, b.TestDate, b.[Pass/Fail] FROM Asset a, PAT b, PPM c, 
(SELECT c.Ass_pri_seq, max(b.TestDate) TestDate from PPM c, PAT b where c.PPM_Pri = b.PPM_pri_seq GROUP BY c.Ass_pri_seq) x 
WHERE a.Ass_Pri = c.Ass_pri_seq AND c.PPM_Pri = b.PPM_pri_Seq and a.Ass_Pri = x.Ass_pri_seq and b.TestDate = x.TestDate 

Open in new window

i am assuming that one one day there could only be one test done for any asset.
Commented:
select AssetID,max(TestDate), [Pass/Fail] from [Assettable] a innerjoin [PPM table] c on a.Ass_Pri=c.Ass_pri_seq  innerjoin [PATtable] b on c.PPM_pri = b.PPM_pri_seq and b.TestDate=max(b.testdate) group by AssetID,[Pass/Fail]

Author

Commented:
Thank you both very much!

@rajeevnandanmishra: Good answer, thanks!

@keyu: Only slight problem I had was that SQL didn't like having an aggregate function in the ON clause but you still gave me the right ideas. Good answer though, thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial