SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('[TestDoc].[A]') IS NOT NULL
  DROP TABLE [TestDoc].[A]


IF SCHEMA_ID('TestDoc') IS NULL
  EXEC('
    CREATE SCHEMA [TestDoc]
  ')
GO

IF OBJECT_ID('[TestDoc].[A]') IS NULL
  
CREATE TABLE [TestDoc].[A]
  (
    [Id]        Int           NOT NULL  IDENTITY(1,1),
    [contractID] int    not null,
    [DataFirst]     Date   NULL,
    [DateEnd]  Date       NULL,  
    [value]    float          NOT NULL,
   PRIMARY KEY CLUSTERED([Id])
  )
GO

INSERT INTO [TestDoc].[A]

SELECT 1,null,'2018-03-10',500
UNION
SELECT 1,null,'2018-03-16',600
UNION
SELECT 1,null, '2018-03-19',900
UNION
SELECT 1,'2018-03-09',null,200
UNION
SELECT 1,'2018-03-17',null,300
UNION
SELECT 1,'2018-03-23',null,400
UNION
SELECT 2, '2018-02-01',null,200
UNION
SELECT 2, '2018-02-05',null,350
UNION
SELECT 2,null,'2018-02-07',250

WITH CTE(CONTRACT_ID,DateStart,DateEnd,Value) AS
(
  SELECT 1 ,null,'2018-03-10',500
UNION
SELECT 1,null,'2018-03-16',600
UNION
SELECT 1,null, '2018-03-19',900
UNION
SELECT 1,'2018-03-09',null,200
UNION
SELECT 1,'2018-03-17',null,300
UNION
SELECT 1,'2018-03-23',null,400
UNION
SELECT 2, '2018-02-01',null,200
UNION
SELECT 2, '2018-02-05',null,350
UNION
SELECT 2,null,'2018-02-07',250
),
C2 AS
(
  SELECT C.CONTRACT_ID,
  ISNULL(C.DATESTART,C.DateEnd)DATESTART,
  ISNULL(C.DATEEND,C.DateStart)DATEEND,
  C.VALUE
  FROM CTE C
),
C3 AS
(
SELECT X.CONTRACT_ID,X.DATESTART,X.DATEEND,X.VALUE,
ROW_NUMBER()OVER(PARTITION BY X.CONTRACT_ID ORDER BY X.DATESTART ASC)XCOL
FROM C2 X  
),
OPENED AS
(
  SELECT C.CONTRACT_ID,C.DateStart,C.VALUE,
  ROW_NUMBER()OVER(PARTITION BY C.CONTRACT_ID ORDER BY C.DATESTART DESC,C.DATEEND ASC)XCOL
  FROM C3 C
  
  
),
CLOSEDINTERVALS AS
(
SELECT C.CONTRACT_ID,X.DATESTART,C.DATEEND,X.Value
FROM C3 C
JOIN C3 X ON C.CONTRACT_ID=X.CONTRACT_ID AND C.XCOL=X.XCOL+1

),
PREV AS
(
SELECT CL.CONTRACT_ID,CL.DATESTART,CL.DATEEND,CL.VALUE
FROM CLOSEDINTERVALS CL

UNION ALL

SELECT OP.CONTRACT_ID,OP.DateStart,NULL DATEEND,OP.Value
FROM OPENED OP
WHERE OP.XCOL=1
)
SELECT P.CONTRACT_ID,P.DATESTART,P.DATEEND,P.VALUE
FROM PREV P
ORDER BY P.CONTRACT_ID ASC,P.DATESTART ASC

SELECT a.contractID,a.d as DateFirst,a.r as DateEnd,a.value
FROM
(
select contractID
,coalesce(dataFirst,dateEnd) as d
,LEAD(coalesce(dataFirst,dateEnd)) OVER(PARTITION BY contractID ORDER BY coalesce(dataFirst,dateEnd)) as r
,value 
from TestDoc.A 
) as a

Order by contractID,d