Re: Quering a state in a set of states

Using SQL Server, I would define the following stored procedure:

CREATE PROC get_latest_item_states
_at_date SMALLDATETIME
AS
SELECT i.item_name, is1.state_value
FROM item AS i

            INNER JOIN
            item_state AS is1
            ON i.item_id = is1.item_id AND
                   is1.state_date = (SELECT MAX(state_date)
                                             FROM item_state AS is2
                                             WHERE is2.item_id = i.item_id
AND
                                                            is2.state_date

Hope this helps.

jag

"Ja La" <none_at_nowhere.dk> wrote in message news:3d68b387$0$43955$edfadb0f_at_dspool01.news.tele.dk...
> Consider this situation:
>
> Some entity ITEM has a time-varing state, ITEMSTATE, represented by to
> tables (ITEM -- 1-N -- ITEMSTATE)
>
> ITEM( item_id, item_name), PK=(item_id)
>
> ITEMSTATE( item_id, state_date, state_value), PK=(item_id, state_date);
> FK=(item_id) to ITEM
>
> A query is wanted that gives the set of (item_name,state_value)-records
for
> a given value of a parameter "date", such that the latest value is chosen;
> ie. state_date<=date and (at most) one record for each item_id.
>
> How can such a (parametrized) query be formulated in SQL?
>
>
Received on Sun Aug 25 2002 - 16:13:56 CEST