-- Oracle

DROP TABLE LOT_LOC;

-- TO_DATE is a reserved word
CREATE TABLE LOT_LOC( 
   FDYD_ID INT, 
   LOT_ID_NUM INT, 
   PEN_ID INT, 
   HD_CNT INT, 
   FROMDATE DATE, 
   TODATE DATE 
);


INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROMDATE, TODATE)
VALUES (1, 137, 1, 17,
   TO_DATE('1998-02-07','YYYY-MM-DD'), TO_DATE('1998-02-18','YYYY-MM-DD'));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROMDATE, TODATE)
VALUES (1, 219, 1, 43,
   TO_DATE('1998-02-25','YYYY-MM-DD'), TO_DATE('1998-03-01','YYYY-MM-DD'));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROMDATE, TODATE)
VALUES (1, 219, 1, 20,
   TO_DATE('1998-03-01','YYYY-MM-DD'), TO_DATE('1998-03-14','YYYY-MM-DD'));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROMDATE, TODATE)
VALUES (1, 219, 2, 23,
   TO_DATE('1998-03-01','YYYY-MM-DD'), TO_DATE('1998-03-14','YYYY-MM-DD'));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROMDATE, TODATE)
VALUES (1, 219, 2, 43,
   TO_DATE('1998-03-14','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

INSERT INTO LOT_LOC (FDYD_ID, LOT_ID_NUM, PEN_ID, HD_CNT, FROMDATE, TODATE)
VALUES (1, 374, 1, 14,
   TO_DATE('1998-02-20','YYYY-MM-DD'), TO_DATE('9999-12-31','YYYY-MM-DD'));

-- 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 TODATE = TO_DATE('9999-12-31','YYYY-MM-DD');

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

SELECT PEN_ID, HD_CNT, FROMDATE, TODATE
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 L1, LOT_LOC 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.TODATE = TO_DATE('9999-12-31','YYYY-MM-DD')
   AND L2.TODATE = TO_DATE('9999-12-31','YYYY-MM-DD');

-- 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 L1, LOT_LOC 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.FROMDATE, L1.TODATE
FROM LOT_LOC L1, LOT_LOC 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.FROMDATE <= L1.FROMDATE
   AND L1.TODATE <= L2.TODATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROMDATE, L2.TODATE
FROM LOT_LOC L1, LOT_LOC 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.FROMDATE > L2.FROMDATE
   AND L2.TODATE < L1.TODATE
   AND L1.FROMDATE < L2.TODATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROMDATE, L1.TODATE
FROM LOT_LOC L1, LOT_LOC 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.FROMDATE > L1.FROMDATE
   AND L1.TODATE < L2.TODATE
   AND L2.FROMDATE < L1.TODATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROMDATE, L2.TODATE
FROM LOT_LOC L1, LOT_LOC 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.FROMDATE >= L1.FROMDATE
   AND L2.TODATE <= L1.TODATE;

-- Using the GREATEST and LEAST functions

SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID,
         GREATEST(L1.FROMDATE, L2.FROMDATE),
         LEAST(L1.TODATE, L2.TODATE)
FROM LOT_LOC L1, LOT_LOC 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 GREATEST(L1.FROMDATE, L2.FROMDATE) <
      LEAST(L1.TODATE, L2.TODATE);

quit