SQL Server: cómo completar los valores de columna que faltan
Avatar SG_
Pregunta contestada

SQL Server: cómo completar los valores de columna que faltan

Tengo un conjunto de registros a nivel de día con 2 columnas:

  • Fecha_factura
  • Importe_factura

Para algunos registros, falta el valor de cantidad_factura.

Necesito completar valores de cantidad_factura donde es NULL usando esta lógica:

  1. Buscar el siguiente invoice_amount disponible (en fechas posteriores a la fecha de registro del valor en blanco)

  2. Para registros con invoice_amount aún en blanco (invoice_amount no presente para fechas futuras), busque la mayoría de los invoice_amount anteriores (en fechas anteriores al espacio en blanco). fecha de valor)

Nota: tenemos varios días consecutivos en los que invoice_amount está en blanco en el conjunto de datos:

Consulte el conjunto de datos de problemas y soluciones adjunto

Mostrar la mejor respuesta

¿Qué versión de SQL Server estás usando?

Avatar SG_

Hola ardilla, estoy usando el servidor sql 2014

Avatar ZLK

Entonces, ¿algo como COALESCE(Invoice_amount, LEAD(Invoice_amount) OVER (ORDER BY Invoice_date), LAG(Invoice_amount) OVER (ORDER BY Invoice_data))?

Avatar SG_

Gracias por las ideas. Puede haber escenarios en los que falte el importe de la factura durante 3 o 4 días consecutivos. Estoy pensando ¿cómo funcionaría entonces?

No es eficiente pero parece funcionar. Prueba:

update test set invoice_amount =   
       coalesce ((select top 1 next.invoice_amount from test next 
                   where next.invoiceDate > test.invoiceDate and next.invoice_amount is not null
                   order by next.invoiceDate),
                (select top 1 prev.invoice_amount from test prev 
                   where prev.invoiceDate < test.invoiceDate and prev.invoice_amount is not null
                   order by prev.invoiceDate desc))
where invoice_amount is null;

utilice APLICACIÓN CRUZADA para encontrar el importe de la factura siguiente y anterior no nulo

update  p
set     Invoice_Amount  = coalesce(nx.Invoice_Amount, pr.Invoice_Amount)
from    Problem p
        outer apply -- Next non null value
        (
            select  top 1 *
            from    Problem x
            where   x.Invoice_Amount    is not null
            and     x.Invoice_Date  > p.Invoice_Date
            order by Invoice_Date
        ) nx
        outer apply -- Prev non null value
        (
            select  top 1 *
            from    Problem x
            where   x.Invoice_Amount    is not null
            and     x.Invoice_Date  < p.Invoice_Date
            order by Invoice_Date desc
        ) pr
where   p.Invoice_Amount    is null

esto actualiza tu tabla. Si necesita una consulta de selección, puede modificarla fácilmente

Según el ejemplo dado, podría usar la función de ventana con self join

update t set t.amount = tt.NewAmount 
from table t
inner join (
    select Dates, coalesce(min(amount) over (order by dates desc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
                           min(amount) over (order by dates asc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) NewAmount
    from table t
) tt on tt.dates = t.dates
where t.amount is null