SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 join newest records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dlorenc
Posting Yak Master

USA
172 Posts

Posted - 05/13/2013 :  17:12:28  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/14/2013 :  00:24:57  Show Profile  Reply with Quote

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))


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000