Free adding values ​​in t-sql

Question

I have a table like this:

Items   Date        Price
1       2016-01-01  10
1       2016-01-02  15
1       2016-01-03  null
1       2016-01-04  null
1       2016-01-05  8
1       2016-01-06  null
1       2016-01-07  null
1       2016-01-08  null
2       2016-01-01  14
2       2016-01-02  7
2       2016-01-03  null
2       2016-01-04  null
2       2016-01-05  16
2       2016-01-06  null
2       2016-01-07  null
2       2016-01-08  5

Now I want to update the null values. The difference between the price before and after zero values ​​should be added evenly.

Example:

1       2016-01-02  15   to
1       2016-01-05  8

15 to 8 = -7

-7/3 = -2.333333

1       2016-01-02  15
1       2016-01-03  12,6666
1       2016-01-04  10,3333
1       2016-01-05  8

Cannot be done with cursors. Recipes will be ok.

Answer 1

This is really where you want the option ignore nullson lag()and lead(). Alas.

An alternative is to use outer apply:

select t.*,
       coalesce(t.price,
                tprev.price +
                 datediff(day, tprev.date, t.date) * (tnext.price - tprev.price) / datediff(day, tprev.date, tnext.date)
               ) as est_price
from t outer apply
     (select top 1 t2.*
      from t t2
      where t2.item = t.item and
            t2.date <= t.date and
            t2.price is not null
      order by t2.date desc
     ) tprev outer apply
     (select top 1 t2.*
      from t t2
      where t2.item = t.item and
            t2.date >= t.date and
            t2.price is not null
      order by t2.date asc
     ) tnext ;

Complex arithmetic simply calculates the difference, dividing by the number of days, and then highlighting the days for the current day.

Answer 2

WITH T1 AS
(
SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Items ORDER BY Date) AS RN,
        FORMAT(ROW_NUMBER() OVER (PARTITION BY Items ORDER BY Date),'D10') + FORMAT(Price,'0000000000.000000') AS RnPr
FROM YourTable 
), T2 AS
(
SELECT *,
        MAX(RnPr) OVER (PARTITION BY Items ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev,
        MIN(RnPr) OVER (PARTITION BY Items ORDER BY Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next
FROM T1
), T3 AS
(
SELECT Items,
       Date,
       Price,
       RnPr, 
       InterpolatedPrice =  IIF(Price IS NOT NULL,prevPrice,prevPrice + (RN - prevRN) * (nextPrice - prevPrice)/NULLIF(nextRN - prevRN,0))
FROM T2
CROSS APPLY (VALUES(CAST(SUBSTRING(prev,11,17) AS decimal(16,6)),
                    CAST(LEFT(prev, 10) AS INT),
                    CAST(SUBSTRING(next,11,17) AS decimal(16,6)),
                    CAST(LEFT(next, 10) AS INT)
                    )) V(prevPrice,prevRN,nextPrice,nextRN)
)
--UPDATE T3 SET Price = InterpolatedPrice
SELECT *
FROM T3
ORDER  BY Items,
          Date 

What returns

row_number (RnPr ). RnPr row_number. MIN MAX NULLS. , MAX(RnPr) UNBOUNDED PRECEDING AND CURRENT ROW NOT NULL, . MIN(RnPr) CURRENT ROW AND UNBOUNDED FOLLOWING.

, , .

, SELECT , UPDATE , .

Answer 3

"YourTable" (4 ) .

, UPDATE WHERE.

Select A.Items,A.Date,
--Update YourTable Set 
       Price = IsNull(A.Price,((DateDiff(DD,B.Date,A.Date)/(DateDiff(DD,B.Date,C.Date)+0.0))*(C.Price - B.Price)) + B.Price)
 From YourTable A
 Outer Apply (Select Top 1 Date,Price from YourTable Where Items=A.Items and Date<A.Date and Price is not Null and A.Price is null Order by Price Desc) B
 Outer Apply (Select Top 1 Date,Price from YourTable Where Items=A.Items and Date>A.Date and Price is not Null and A.Price is null Order by Price) C
--Where Price is NULL

01/06 01/08 1. , .

Answer 4

You can do this using a series of window functions in common table expressions.

• T1assigns a line number to each line ( rn)
• T2finds the first number of the zero line Pricebefore and after the current line ( rnb/ rna)
• T3calculates adjusted Priceby viewing prices before and after

---

declare @T table (Items int, Date date, Price float)
insert into @T (Items, Date, Price) values
(1, '2016-01-01', 10), (1, '2016-01-02', 15), (1, '2016-01-03', null), (1, '2016-01-04', null), (1, '2016-01-05', 8), (1, '2016-01-06', null), (1, '2016-01-07', null), (1, '2016-01-08', null), (2, '2016-01-01', 14), (2, '2016-01-02', 7), (2, '2016-01-03', null), (2, '2016-01-04', null), (2, '2016-01-05', 16), (2, '2016-01-06', null), (2, '2016-01-07', null), (2, '2016-01-08', 5)

;with T1 as 
(
    select *,
        row_number() over (order by Items, Date) as rn
    from @T     
),
T2 as 
(
    select *,
        max(case when price is null then null else rn end) over (partition by Items order by Date) as rnb,
        min(case when price is null then null else rn end) over (partition by Items order by Date desc) as rna
    from T1 
)
select Items, Date,

    isnull(price,
        lag(Price, rn-rnb, Price) over (order by rn) -
        (
            lag(Price, rn-rnb, Price) over (order by rn) -
            lead(Price, rna-rn, Price) over (order by rn)
        ) / (rna-rnb) * (rn-rnb)        
    ) as Price

from T2
order by Items, Date