Microsoft SQL Server forum - Query/View: The 2 newest periods for each indicator

Author

Query/View: The 2 newest periods for each indicator

Ryan Dahl

2006-03-24, 8:25 pm

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 Actua
l
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?

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

Alternatively a View with 2 posts for each performace-indicator.

Thanks in advance
Ryan

Hugo Kornelis

2006-03-24, 8:25 pm

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 Actua
l
>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

Ryan Dahl

2006-03-25, 3:27 am

Hi Hugo,

Thanks a lot. I got them both working without any hassle.

>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

I find this to be quite clever - had to look at it some time to figure
out how it works.

>
>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)
>
Works as well - minor adjustment needed: Move lines 5-8 to the end.

Regards
Ryan

--CELKO--

2006-03-25, 11:28 am

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

I am a little confused on this. Aren't "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.

Doug

2006-03-25, 11:28 am

celko, have you actually ever HELPED anyone on this list?????

i'd be curious to review a link where your original SQL code, written
in the past 10 years, is deomonstrated.

thx,
doug

Hugo Kornelis

2006-03-27, 8:27 pm

On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:

>Works as well - minor adjustment needed: Move lines 5-8 to the end.

Hi Ryan,

That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.

This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.

Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.

--
Hugo Kornelis, SQL Server MVP

Ryan Dahl

2006-03-28, 11:29 am

On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hugo@perFact.REMOVETHIS.info.INVALID> wrote:

>On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>
>
>Hi Ryan,
>
>That changes the meaning of the query - the place where you put those
>lines dictates what will happen for a kpiID that has only one row.
>
>This one row is by definition the latest - but there's no second latest.
>If you use the query I suggested, you'll get this kpiID in your result,
>with it's only row as last measurement and NULLs as it's second latest
>measurement.
>
>Your version (after moving those rows) will exclude any kpiID with only
>one row. Only kpiIDs with two or more measurements will be displayed. If
>that is indeed your requirement, then you can safely move these lines.
>And you can change the LEFT JOIN in an INNER JOIN as well, to get some
>performance gain.

Hi Hugo,

thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.

Thanks again.
Ryan

Sponsored Links