#!/usr/bin/sh -x

# DB2

echo Cleanup

db2 "DROP TABLE LOT_LOC"

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

db2 "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'))"

db2 "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'))"

db2 "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'))"

db2 "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'))"

db2 "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'))"

db2 "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 in each pen?

db2 "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.

db2 "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?

db2 "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?

db2 "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?

db2 "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

db2 "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.FROM_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.TO_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 the CASE construct

db2 "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)"