clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1451419 fiddles created (18721 in the last week).

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) );
 hidden batch(es)


-- (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');
22 rows affected
 hidden batch(es)


-- (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 );
TaskNumber CurrentStateCode SpecifiedDateTime
1750 C 11/12/2016 07:24:07
 hidden batch(es)


-- (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 );
TaskNumber CurrentStateCode SpecifiedDateTime
1750 C 11/12/2016 07:24:07
1 C 16/11/2016 05:12:06
 hidden batch(es)


-- (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;
TaskNumber Description StateCode StartDateTime EndDateTime
1750 Time series demo C 11/12/2016 07:24:07
1750 Time series demo R 09/12/2016 06:24:07 11/12/2016 07:24:07
1750 Time series demo B 08/12/2016 19:46:01 09/12/2016 06:24:07
1750 Time series demo R 08/12/2016 12:12:09 08/12/2016 19:46:01
1750 Time series demo C 07/12/2016 07:33:08 08/12/2016 12:12:09
1750 Time series demo R 04/12/2016 10:57:01 07/12/2016 07:33:08
1750 Time series demo B 02/12/2016 09:12:05 04/12/2016 10:57:01
1750 Time series demo O 01/12/2016 16:58:12 02/12/2016 09:12:05
 hidden batch(es)


-- (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;
TaskNumber Description StateCode StartDateTime EndDateTime
1750 Time series demo C 11/12/2016 07:24:07
1750 Time series demo R 09/12/2016 06:24:07 11/12/2016 07:24:07
1750 Time series demo B 08/12/2016 19:46:01 09/12/2016 06:24:07
1750 Time series demo R 08/12/2016 12:12:09 08/12/2016 19:46:01
1750 Time series demo C 07/12/2016 07:33:08 08/12/2016 12:12:09
1750 Time series demo R 04/12/2016 10:57:01 07/12/2016 07:33:08
1750 Time series demo B 02/12/2016 09:12:05 04/12/2016 10:57:01
1750 Time series demo O 01/12/2016 16:58:12 02/12/2016 09:12:05
1 Time series test C 16/11/2016 05:12:06
1 Time series test O 15/11/2016 08:17:02 16/11/2016 05:12:06
1 Time series test R 14/11/2016 15:10:01 15/11/2016 08:17:02
1 Time series test O 13/11/2016 10:28:01 14/11/2016 15:10:01
1 Time series test C 12/11/2016 09:01:02 13/11/2016 10:28:01
1 Time series test R 11/11/2016 08:42:07 12/11/2016 09:01:02
1 Time series test B 10/11/2016 06:45:05 11/11/2016 08:42:07
1 Time series test O 09/11/2016 06:12:03 10/11/2016 06:45:05
 hidden batch(es)