0

I have two tables in a stage-gate project management system, one (simplified) table containing a project ID and actual gate dates for each gate, 1-5. In the other I have a historic record of all forecast data; Projected Revenue, Projected Margins, Forecast Year, etc. Each time a forecast is updated, it records the new forecast values, the time stamp of the change, and the project ID. The requirement is to retrieve all metric values for the latest update prior to the actual gate date recorded in the first table. An example, Project 100 has a Gate 2 date of 2014-12-18. I need to retrieve the most recent values prior to that date.

Gate Date Table:

ProjectID     InternalGate2
---------     -------------
100           2014-12-18
2000          2013-01-15

Historic Metric Table:

ProjectID     Metric    MetricYear    LastUpdated    MetricValue
---------     ------    ----------    -----------    -----------
100           Sales     2015          2013-09-05     125000
100           Sales     2016          2013-09-05     230000
100           GM        2015          2013-09-05     .48
100           GM        2016          2013-09-05     .49
100           Sales     2015          2014-05-26     200000
100           Sales     2016          2014-05-26     300000
100           GM        2015          2014-05-26     .50
100           GM        2016          2014-05-26     .51
100           Sales     2015          2015-01-28     300000
100           Sales     2016          2015-01-28     400000
100           GM        2015          2015-01-28     .55
100           GM        2016          2015-01-28     .56

2000          Sales     2014          2012-11-23     200000
2000          Sales     2015          2012-11-23     300000
2000          Sales     2016          2012-11-23     310000
2000          GM        2014          2012-11-23     .75
2000          GM        2015          2012-11-23     .77
2000          GM        2016          2012-11-23     .77
2000          Sales     2015          2013-02-11     450000
2000          Sales     2016          2013-02-11     450000
2000          Sales     2017          2013-02-11     500000
2000          GM        2015          2013-02-11     .68
2000          GM        2016          2013-02-11     .69
2000          GM        2017          2013-02-11     .70

For this example the results set would be the four rows for Project 100 with the LastUpdated Date of 2014-05-26 as this was the last update prior to 2014-12-18 and the first six rows of data for Project 2000 updated 2012-11-23.

Any guidance would be greatly appreciated.

scottM
  • 3
  • 2

1 Answers1

0

The CTE could be a subquery if you prefer, but this works, basically just using two joins.

;WITH CTE as 
(select h.ProjectID,MAX(LastUpdated) as LatestUpdate
from Historic h
inner join Gate g
on h.ProjectID = g.ProjectID
and h.LastUpdated <= g.InternalGate2
group by h.ProjectID)

select ProjectID,LastUpdated
from Historic h
inner join CTE c
on h.ProjectID = c.ProjectID
and h.LastUpdated = c.LatestUpdate
Sam Cohen-Devries
  • 2,025
  • 2
  • 18
  • 35
  • Thank you for the quick response! It appears to be working. I'm now trying to optimize the query as it takes approximately an hour to return a result set. I think if I reduce the the initial table expression down to distinct LastUpdate values it will help significantly. Each time the forecast is updated, approximately 120 metrics (rows) are added all with the same LastUpdate stamp. If I limit the initial load to distinct dates, I'm sure it will be much less of a hit to the MAX function. – scottM Oct 16 '15 at 20:14