-- standard SQL-92

-- Prevent value-equivalent rows in NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   UNIQUE (Name, Status)
)

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious', DATE '1997-11-19', DATE '1997-11-21')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious', DATE '1997-11-19', DATE '1997-11-27')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious', DATE '1997-11-19', DATE '1997-11-25')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious', DATE '1997-11-19', DATE '1997-11-28')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& critical', DATE '1997-11-19', DATE '1997-11-20')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& serious', DATE '1997-11-20', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair', DATE '1997-11-21', DATE '1998-01-03')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-11-27', DATE '1998-01-11')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')

-- Prevent nonsequenced duplicates in NICUStatus

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   UNIQUE (Name, Status, from_date, to_date)
)

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious', DATE '1997-11-19', DATE '1997-11-21')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious', DATE '1997-11-19', DATE '1997-11-27')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious', DATE '1997-11-19', DATE '1997-11-25')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious', DATE '1997-11-19', DATE '1997-11-28')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& critical', DATE '1997-11-19', DATE '1997-11-20')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& serious', DATE '1997-11-20', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair', DATE '1997-11-21', DATE '1998-01-03')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-11-27', DATE '1998-01-11')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')

-- Prevent current duplicates in NICUStatus

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   CHECK (NOT EXISTS (SELECT N1.SSN
                     FROM NICUStatus AS N1
                     WHERE 1 < (SELECT COUNT(Name)
                        FROM NICUStatus AS N2
                        WHERE N1.Name = N2.Name AND N1.Status = N2.Status
                           AND N1.from_date <= CURRENT_DATE
                           AND CURRENT_DATE < N1.to_date
                           AND N2.from_date <= CURRENT_DATE
                           AND CURRENT_DATE < N2.to_date)))
)

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious', DATE '1997-11-19', DATE '1997-11-21')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious', DATE '1997-11-19', DATE '1997-11-27')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious', DATE '1997-11-19', DATE '1997-11-25')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious', DATE '1997-11-19', DATE '1997-11-28')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& critical', DATE '1997-11-19', DATE '1997-11-20')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& serious', DATE '1997-11-20', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair', DATE '1997-11-21', DATE '1998-01-03')
--will cause an error if today is 1998-01-06
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-11-27', DATE '1998-01-11')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')

-- Prevent current duplicates in NICUStatus, assuming no future data

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   UNIQUE (Name, Status, to_date)
)

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious', DATE '1997-11-19', DATE '1997-11-21')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious', DATE '1997-11-19', DATE '1997-11-27')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious', DATE '1997-11-19', DATE '1997-11-25')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious', DATE '1997-11-19', DATE '1997-11-28')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& critical', DATE '1997-11-19', DATE '1997-11-20')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& serious', DATE '1997-11-20', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair', DATE '1997-11-21', DATE '1998-01-03')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-11-27', DATE '1998-01-11')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')

-- Prevent sequenced duplicates in NICUStatus

DROP TABLE NICUStatus

CREATE TABLE NICUStatus (
   Name CHAR(15),
   Status CHAR(8),
   from_date DATE,
   to_date DATE,
   CHECK (NOT EXISTS (SELECT N1.Name
                     FROM NICUStatus AS N1
                     WHERE 1 < (SELECT COUNT(Name)
                        FROM NICUStatus AS N2
                        WHERE N1.Name = N2.Name AND N1.Status = N2.Status
                           AND N1.from_date < N2.to_date AND N2.from_date < N1.to_date)))
)

INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'serious', DATE '1997-11-19', DATE '1997-11-21')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'serious', DATE '1997-11-19', DATE '1997-11-27')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Natalie Sue', 'serious', DATE '1997-11-19', DATE '1997-11-25')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kelsey Ann', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Brandon James', 'serious', DATE '1997-11-19', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Nathan Roy', 'serious', DATE '1997-11-19', DATE '1997-11-28')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& critical', DATE '1997-11-19', DATE '1997-11-20')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Joel Steven& serious', DATE '1997-11-20', DATE '1997-11-26')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Kenneth Robert', 'fair', DATE '1997-11-21', DATE '1998-01-03')
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-11-27', DATE '1998-01-11')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')
-- will cause an error
INSERT INTO NICUStatus (Name, Status, from_date, to_date)
VALUES ('Alexis May', 'fair', DATE '1997-12-02', DATE '9999-12-31')