join newest records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join newest records

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2013-05-13 : 17:12:28

Given two tables containing monthly snapshots of project information and the status reports that go with each project... I have the code below in a VIEW that will select the projects from the 'newest' snapshot..

how do I add a join with the corresponding 'newest' status reports for each project?

SELECT TOP (100) PERCENT PIN, ProgramName, PlannedStartDate, PlannedEndDate, PercentComplete
FROM dbo.EPM_Project
WHERE (DataMartDate =
(SELECT TOP (1) DataMartDate FROM dbo.EPM_Project AS EPM_Project_1
ORDER BY DataMartDate DESC))

table statusreports would be joined to table project by field project_ID...field datamartdate in table statusreports would hold the date of the snapshot of the statusreport for each project (each project has multiple status reports (history) but only one MOST CURRENT status report ie. statusreports.max(datamartdate) = most current status report for a project)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:24:57

[code]
SELECT TOP (100) PERCENT PIN, ProgramName, PlannedStartDate, PlannedEndDate, PercentComplete
FROM dbo.EPM_Project p
CROSS APPLY (SELECT TOP 1 *
FROM statusreports
WHERE project_ID = p.project_ID
ORDER BY datamartdate DESC)s
WHERE (DataMartDate =
(SELECT TOP (1) DataMartDate FROM dbo.EPM_Project AS EPM_Project_1
ORDER BY DataMartDate DESC))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources