-- standard SQL-92

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 '1998-02-07', DATE '1998-02-18')
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 1, 43, DATE '1998-02-25', DATE '1998-03-01')
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 1, 20, DATE '1998-03-01', DATE '1998-03-14')
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 2, 23, DATE '1998-03-01', DATE '1998-03-14')
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 219, 2, 43, DATE '1998-03-14', DATE '9999-12-31')
INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
VALUES (1, 374, 1, 14, DATE '1998-02-20', DATE '9999-12-31')

-- 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 '9999-12-31'

-- 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 '9999-12-31'
   AND L2.TO_DATE = DATE '9999-12-31'

-- 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, L2.LOT, 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.START_DATE <= L1.FROM_DATE
   AND L1.TO_DATE <= L2.TO_DATE
UNION
SELECT L1.LOT, L2.LOT, 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.TO_DATE
   AND L1.FROM_DATE < L2.TO_DATE
UNION
SELECT L1.LOT, L2.LOT, 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, L2.LOT, 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 the CASE construct

SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID,
         CASE WHEN L1.FROM_DATE > L2.FROM_DATE
               THEN L1.FROM_DATE
            ELSE L2.FROM_DATE END,
         CASE WHEN L1.TO_DATE > L2.TO_DATE
               THEN L2.TO_DATE
            ELSE L1.TO_DATE END
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 (CASE WHEN L1.FROM_DATE > L2.FROM_DATE
               THEN L1.FROM_DATE
            ELSE L2.FROM_DATE END) <
         (CASE WHEN L1.TO_DATE > L2.TO_DATE
               THEN L2.TO_DATE
            ELSE L1.TO_DATE END)

-- Using an SQL/PSM function

CREATE FUNCTION first_instant (one DATE, two DATE)
RETURNS DATE
LANGUAGE SQL

RETURN CASE WHEN one > two
               THEN one
            ELSE two END

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)