CREATE TABLE Task ( TaskNumber INT NOT NULL, Description CHAR(90) NOT NULL, Etcetera CHAR(30) NOT NULL, CreatedDateTime DATETIME NOT NULL, CONSTRAINT Task_PK PRIMARY KEY (TaskNumber) ); CREATE TABLE State ( StateCode CHAR(1) NOT NULL, Name CHAR(30) NOT NULL, Description CHAR(60) NOT NULL, CONSTRAINT State_PK PRIMARY KEY (StateCode), CONSTRAINT State_AK1 UNIQUE (Name), CONSTRAINT State_AK2 UNIQUE (Description) ); CREATE TABLE StateSpecification ( TaskNumber INT NOT NULL, SpecifiedDateTime DATETIME NOT NULL, StateCode CHAR(1) NOT NULL, CONSTRAINT StateSpecification_PK PRIMARY KEY (TaskNumber, SpecifiedDateTime), CONSTRAINT StateSpecification_to_Task_FK FOREIGN KEY (TaskNumber) REFERENCES Task (TaskNumber), CONSTRAINT StateSpecification_to_State FOREIGN KEY (StateCode) REFERENCES State (StateCode) ); CREATE TABLE TaskComment ( TaskNumber INT NOT NULL, CreatedDateTime DATETIME NOT NULL, Body TEXT NOT NULL, CONSTRAINT TaskComment_PK PRIMARY KEY (TaskNumber, CreatedDateTime), CONSTRAINT TaskComment_to_Task FOREIGN KEY (TaskNumber) REFERENCES Task (TaskNumber) );

-- (1) INSERT INTO State (StateCode, Name, Description) VALUES ('O', 'Open', 'Indicates that W'), ('B', 'Begun', 'Indicates that X'), ('R', 'In review', 'Indicates that Y'), ('C', 'Completed', 'Indicates that Z'); -- (2) INSERT INTO Task (TaskNumber, Description, Etcetera, CreatedDateTime) VALUES (1750, 'Time series demo', 'DBA.SE', '2017-02-07 05:12:04.000'), (1, 'Time series test', 'Evaluation', '2017-02-06 09:46:04.000'); -- (3) INSERT INTO StateSpecification (TaskNumber, SpecifiedDateTime, StateCode) VALUES (1750, '2016-12-01 16:58:12.000', 'O'), (1750, '2016-12-02 09:12:05.000', 'B'), (1750, '2016-12-04 10:57:01.000', 'R'), (1750, '2016-12-07 07:33:08.000', 'C'), (1750, '2016-12-08 12:12:09.000', 'R'), (1750, '2016-12-08 19:46:01.000', 'B'), (1750, '2016-12-09 06:24:07.000', 'R'), (1750, '2016-12-11 07:24:07.000', 'C'), (1, '2016-11-09 06:12:03.000', 'O'), (1, '2016-11-10 06:45:05.000', 'B'), (1, '2016-11-11 08:42:07.000', 'R'), (1, '2016-11-12 09:01:02.000', 'C'), (1, '2016-11-13 10:28:01.000', 'O'), (1, '2016-11-14 15:10:01.000', 'R'), (1, '2016-11-15 08:17:02.000', 'O'), (1, '2016-11-16 05:12:06.000', 'C');

-- (a) SELECT T.TaskNumber, SS.StateCode AS CurrentStateCode, SS.SpecifiedDateTime FROM Task T JOIN StateSpecification SS ON T.TaskNumber = SS.TaskNumber WHERE T.TaskNumber = 1750 AND SS.SpecifiedDateTime = ( SELECT MAX(SpecifiedDateTime) FROM StateSpecification InnerSS WHERE T.TaskNumber = InnerSS.TaskNumber );

-- (b) SELECT T.TaskNumber, SS.StateCode AS CurrentStateCode, SS.SpecifiedDateTime FROM Task T JOIN StateSpecification SS ON T.TaskNumber = SS.TaskNumber WHERE SS.SpecifiedDateTime = ( SELECT MAX(SpecifiedDateTime) FROM StateSpecification InnerSS WHERE T.TaskNumber = InnerSS.TaskNumber );

-- (c) SELECT T.TaskNumber, T.Description, SS.StateCode, SS.SpecifiedDateTime AS StartDateTime, ( SELECT MIN(SpecifiedDateTime) FROM StateSpecification InnerSS WHERE T.TaskNumber = InnerSS.TaskNumber AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime ) AS EndDateTime FROM Task T JOIN StateSpecification SS ON T.TaskNumber = SS.TaskNumber WHERE T.TaskNumber = 1750 ORDER BY StartDateTime DESC;

-- (d) SELECT T.TaskNumber, T.Description, SS.StateCode, SS.SpecifiedDateTime AS StartDateTime, ( SELECT MIN(SpecifiedDateTime) FROM StateSpecification InnerSS WHERE T.TaskNumber = InnerSS.TaskNumber AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime ) AS EndDateTime FROM Task T JOIN StateSpecification SS ON T.TaskNumber = SS.TaskNumber ORDER BY StartDateTime DESC;