SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
    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');    
22 rows affected

-- (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
2016-12-11 07:24:07.000


-- (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
2016-12-11 07:24:07.000

1
C
2016-11-16 05:12:06.000


-- (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
2016-12-11 07:24:07.000
null

1750
Time series demo
R
2016-12-09 06:24:07.000
2016-12-11 07:24:07.000

1750
Time series demo
B
2016-12-08 19:46:01.000
2016-12-09 06:24:07.000

1750
Time series demo
R
2016-12-08 12:12:09.000
2016-12-08 19:46:01.000

1750
Time series demo
C
2016-12-07 07:33:08.000
2016-12-08 12:12:09.000

1750
Time series demo
R
2016-12-04 10:57:01.000
2016-12-07 07:33:08.000

1750
Time series demo
B
2016-12-02 09:12:05.000
2016-12-04 10:57:01.000

1750
Time series demo
O
2016-12-01 16:58:12.000
2016-12-02 09:12:05.000


-- (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
2016-12-11 07:24:07.000
null

1750
Time series demo
R
2016-12-09 06:24:07.000
2016-12-11 07:24:07.000

1750
Time series demo
B
2016-12-08 19:46:01.000
2016-12-09 06:24:07.000

1750
Time series demo
R
2016-12-08 12:12:09.000
2016-12-08 19:46:01.000

1750
Time series demo
C
2016-12-07 07:33:08.000
2016-12-08 12:12:09.000

1750
Time series demo
R
2016-12-04 10:57:01.000
2016-12-07 07:33:08.000

1750
Time series demo
B
2016-12-02 09:12:05.000
2016-12-04 10:57:01.000

1750
Time series demo
O
2016-12-01 16:58:12.000
2016-12-02 09:12:05.000

1
Time series test
C
2016-11-16 05:12:06.000
null

1
Time series test
O
2016-11-15 08:17:02.000
2016-11-16 05:12:06.000

1
Time series test
R
2016-11-14 15:10:01.000
2016-11-15 08:17:02.000

1
Time series test
O
2016-11-13 10:28:01.000
2016-11-14 15:10:01.000

1
Time series test
C
2016-11-12 09:01:02.000
2016-11-13 10:28:01.000

1
Time series test
R
2016-11-11 08:42:07.000
2016-11-12 09:01:02.000

1
Time series test
B
2016-11-10 06:45:05.000
2016-11-11 08:42:07.000

1
Time series test
O
2016-11-09 06:12:03.000
2016-11-10 06:45:05.000
TaskNumber	Description	StateCode	StartDateTime	EndDateTime
1750	Time series demo	C	2016-12-11 07:24:07.000	null
1750	Time series demo	R	2016-12-09 06:24:07.000	2016-12-11 07:24:07.000
1750	Time series demo	B	2016-12-08 19:46:01.000	2016-12-09 06:24:07.000
1750	Time series demo	R	2016-12-08 12:12:09.000	2016-12-08 19:46:01.000
1750	Time series demo	C	2016-12-07 07:33:08.000	2016-12-08 12:12:09.000
1750	Time series demo	R	2016-12-04 10:57:01.000	2016-12-07 07:33:08.000
1750	Time series demo	B	2016-12-02 09:12:05.000	2016-12-04 10:57:01.000
1750	Time series demo	O	2016-12-01 16:58:12.000	2016-12-02 09:12:05.000