-- vtstate article
-- proposed SQL3

CREATE TABLE LOT_LOC (
   FDYD_ID INT,
   LOT_ID_NUM INT,
   PEN_ID INT,
   HD_CNT INT
) AS VALIDTIME PERIOD(DATE)

VALIDTIME PERIOD '[1998-02-07' - 1998-02-18)'
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT)
VALUES (1, 137, 1, 17)
VALIDTIME PERIOD '[1998-02-25' - 1998-03-01)'
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT)
VALUES (1, 219, 1, 43)
VALIDTIME PERIOD '[1998-03-01' - 1998-03-14)'
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT)
VALUES (1, 219, 1, 20)
VALIDTIME PERIOD '[1998-03-01' - 1998-03-14)'
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT)
VALUES (1, 219, 2, 23)
VALIDTIME PERIOD '[1998-03-14' - 9999-12-31)'
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT)
VALUES (1, 219, 2, 43)
VALIDTIME PERIOD '[1998-02-20' - 9999-12-31)'
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT)
VALUES (1, 374, 1, 14)

-- How many head of cattle from lot 219 in yard 1 are (currently) in each pen?

SELECT PEN_ID, HD_CNT
FROM LOT_LOC
WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219

-- Give the history of how many head of cattle from lot 219 in yard 1 were
-- in each pen.

VALIDTIME SELECT PEN_ID, HD_CNT
FROM LOT_LOC
WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219

-- How many head of cattle from lot 219 in yard 1 were, at some time, in
-- each pen?

NONSEQUENCED VALIDTIME SELECT PEN_ID, HD_CNT
FROM LOT_LOC
WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219

-- Which lots are (currently) in the same pen?

VALIDTIME SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID
FROM LOT_LOC AS L1, LOT_LOC AS L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
   AND L1.FDYD_ID = L2.FDYD_ID
   AND L1.PEN_ID = L2.PEN_ID

-- Which lots were in the same pen, perhaps at different times?

NONSEQUENCED VALIDTIME SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID
FROM LOT_LOC AS L1, LOT_LOC AS L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
   AND L1.FDYD_ID = L2.FDYD_ID
   AND L1.PEN_ID = L2.PEN_ID

-- Give the history of lots being co-resident in a pen

VALIDTIME SELECT L1.LOT, L2.LOT, L1.PEN_ID
FROM LOT_LOC AS L1, LOT_LOC AS L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
   AND L1.FDYD_ID = L2.FDYD_ID
   AND L1.PEN_ID = L2.PEN_ID