-- Informix

CREATE TABLE LOT_LOC(
   FDYD_ID INT,
   LOT_ID_NUM INT,
   PEN_ID INT,
   HD_CNT INT,
   FROM_DATE DATE,
   TO_DATE DATE
);

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 137, 1, 17, DATE("02/07/98"), DATE("02/18/98"));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 1, 43, DATE("02/25/98"), DATE("03/01/98"));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 1, 20, DATE("03/01/98"), DATE("03/14/98"));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 2, 23, DATE("03/01/98"), DATE("03/14/98"));

-- forever/now is denoted with 12/31/9999

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 2, 43, DATE("03/14/98"),
	DATE("12/31/00")+INTERVAL(8099-0) YEAR TO MONTH));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 374, 1, 14, DATE("02/20/98"),
	DATE("12/31/00")+INTERVAL(8099-0) YEAR TO MONTH));

-- 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
   AND TO_DATE = DATE("12/31/00")+INTERVAL(8099-0) YEAR TO MONTH;

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

SELECT PEN_ID, HD_CNT, FROM_DATE, TO_DATE
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?

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?

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
   AND L1.TO_DATE = DATE("12/31/00")+INTERVAL(8099-0) YEAR TO MONTH
   AND L2.TO_DATE = DATE("12/31/00")+INTERVAL(8099-0) YEAR TO MONTH;

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

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

SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROM_DATE, L1.TO_DATE
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
   AND L2.FROM_DATE <= L1.TO_DATE
   AND L1.TO_DATE <= L2.TO_DATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROM_DATE, L2.TO_DATE
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
   AND L1.FROM_DATE > L2.FROM_DATE
   AND L2.TO_DATE < L1.FROM_DATE
   AND L1.FROM_DATE < L2.TO_DATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROM_DATE, L1.TO_DATE
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
   AND L2.FROM_DATE > L1.FROM_DATE
   AND L1.TO_DATE < L2.TO_DATE
   AND L2.FROM_DATE < L1.TO_DATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROM_DATE, L2.TO_DATE
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
   AND L2.FROM_DATE >= L1.FROM_DATE
   AND L2.TO_DATE <= L1.TO_DATE;

-- Using procedures

CREATE PROCEDURE first_instant (one DATE, two DATE)
  RETURNING DATE;
  IF one < two THEN RETURN one;
  ELSE RETURN two;
  ENDIF
END PROCEDURE;

CREATE PROCEDURE last_instant (one DATE, two DATE) RETURNING DATE
  IF one > two THEN RETURN one,
  ELSE RETURN two
  ENDIF
END PROCEDURE;

SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID,
         last_instant(L1.FROM_DATE, L2.FROM_DATE),
         first_instant(L1.TO_DATE, L2.TO_DATE)
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
   AND last_instant(L1.FROM_DATE, L2.FROM_DATE) <
      first_instant(L1.TO_DATE, L2.TO_DATE);