|
|
Posted by Hugo Kornelis on 03/25/06 00:32
On Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote:
>Hi,
>
>I'm working on a simple performance-program, where I need to extract
>information from the 2 newest periods for every performance-indicator
>- And from there calculate a trend between these results.
>
>The problem is, that I can't find a simple way to extract the 2 latest
>results.
>
>The Table (Table1) looks like this:
>kpiID periodID Actual
>Acceleration 2 3
>Acceleration 5 4
>Speed 1 100
>Speed 4 200
>Speed 7 220
>Speed 9 180
>Weight 1 22
>Weight 3 32
>Weight 7 21
>Weight 10 33
>
>If I want to extract the newest I use something like this (made it in
>MS Access, so the syntax might differ slightly from SQLServer):
>
>SELECT table1.kpiID, table1.periodID, table1.Actual
>FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
>table1 as t WHERE t.kpiID=table1.kpiID);
>
>BUT - how how do I get the second-newest period as well?
Hi Ryan,
SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2
>
>Preferably I would like the final result to be a View with the
>following fields:
>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>Actual_sec_newest
In that case, try this instead:
SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
FROM table1 AS a
LEFT JOIN table1 AS b
ON b.kpiID = a.kpiID
AND b.periodID = (SELECT MAX(c.periodID)
FROM table1 AS c
WHERE c.kpiID = a.kpiID
AND c.periodID < a.periodID)
WHERE a.periodID = (SELECT MAX(t.periodID)
FROM table1 AS t
WHERE t.kpiID = a.kpiID)
(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|