Sql-server – SQL update next row in select

sql serversql-server-2008

I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data.

Each row has a field for KM (mileage in KM metric unit), prevMileage (mileage of previous row) and nextMileage (mileage of the next row). The prev and next fields are calculated in a list before this one. The problem with this is that if the KM for a row is NULL the prevmileage of the next row and the nextmileage of the previous row will be NULL as well.

To make my calculation correct I need to add the previous mileage to the calculated value I make for each row. But if there are multiple NULL rows after each other, this is impossible.

What I need now is a way to update the 'previousmileage' field of the next row.

This is the code I have so far and a preview of the table:

Date      |     KM     |   prevmileage    | nextmpileage
--------------------------------------------------------
01/01/2008|     5000   |       4500       |     55000

Code

    , CalculatedList as (
SELECT
    PNL.MonthDate,
    PNL.FK_CarID,
    PNL.AllocationID,
    PNL.CO2,
    PNL.KM,
    PNL.beginMonth ,
    (CASE   WHEN PNL.KM IS NULL 
            THEN (CASE  WHEN PNL.PrevMileage IS NULL
                        THEN (CASE  WHEN PNLnext.PrevMileage IS NULL --Here I want to update the prevmilage of the next row
                                    THEN (CASE  WHEN PNL.NextMileage IS NULL
                                            THEN 0
                                            ELSE ((PNL.NextMileage / DATEDIFF(DAY, PNL.PrevMonthDate, PNL.NextMonthDate)) * DATEDIFF(DAY, PNL.PrevMonthDate, PNL.MonthDate))
                                            END) 
                                    ELSE 0
                                    END)
                        ELSE (CASE  WHEN PNL.NextMileage IS NULL
                                    THEN PNL.PrevMileage
                                    ELSE (CASE  WHEN PNL.PrevMileage = 0 OR PNL.NextMileage = 0
                                                THEN (CASE  WHEN PNL.NextMileage = 0
                                                            THEN 0
                                                            ELSE (PNL.NextMileage / 2)
                                                            END)
                                                ELSE ((PNL.NextMileage - PNL.PrevMileage) / DATEDIFF(DAY, PNL.NextMonthDate, PNL.PrevMonthDate)) * DATEDIFF(DAY, PNL.MonthDate, PNL.PrevMonthDate)
                                                END)
                                    END) 
                        END)
            ELSE PNL.KM
            END ) as Calculated,
    PNL.NextMileage,
    PNL.PrevMileage,
    PNL.PrevMonthDate,
    PNL.NextMonthDate,
    (CASE   WHEN PNL.KM IS NULL 
            THEN 'N'
            ELSE 'Y'
            END) AS RealData,
    PNLnext.PrevMileage as test
FROM
    PrevNextFullList as PNL outer apply
     (select top 1 ml2.*
      from PrevNextFullList ml2
      where ml2.FK_CarID = PNL.FK_CarID and
            ml2.MonthDate > PNL.MonthDate
      order by ml2.MonthDate desc
     ) PNLnext
)

Best Answer

Do you actually need to persist the prev and next columns in the data? Why not use Window functions on retrieval of data to get what you need from your base table? I've made a bit of a guess on what your schema looks like in it's most basic form but this should help you. If not post CREATE statements for all tables involved and I'll edit the answer.

SELECT  MonthDate
        , CarId
        , KM
        , LAG(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) Prev
        , LEAD(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) [next]


FROM    PrevNextFullList

If you do need to persist the columns then use this statement to create a VIEW

UPDATE You would need to upgrade to SQL 2012 to do this though.

Related Solutions

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10 
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name 
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;

SQL Server 2008 – How to Compare Current Row with Previous or Next Row

If I understand correctly, you need:

select
    employeeID, 
    DateAndTime, 
    lastChange,
    absenceId, 
    KeyNo, 
    fromDate = convert(varchar(10), fd, 20), 
    tillDate = convert(varchar(10), td, 20)
from
(
    select 
        em.employeeID, 
        at.DateAndTime, 
        lastChange = at.LETZT_AEND, 
        absenceId = ab.IDFEZE, 
        KeyNo = at.EINTRKEY, 
        fd = convert(date, ab.fromDate), 
        td = convert(date, ab.tillDate),
        rn = row_number() over 
          (partition by em.employeeId, 
                        convert(date, ab.fromDate), 
                        convert(date, ab.tillDate)
           order by at.DateAndTime desc, 
                    at.LETZT_AEND desc)
    from 
        LogTimeAttendance as at
      join LogAbsence as ab
        on at.KeyNo = ab.KeyNo
      join Employee as em
        on em.employeeID = at.employeeID
    -- where 
    --     em.employeeID = 'R0994'
) as a
where 
    rn = 1
order by 
    employeeID, fd, td ;

Based on the sample you provided, the partition by could possibly be based on the (simpler) partition by em.employeeId, at.EINTRKEY.

If fromDate and tillDate have no time parts (always YYYY-MM-DD 00:00:00, then the convert to (date ...) are not needed. But then, why isn't the type of these columns DATE?

Best Answer

Related Solutions

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

SQL Server 2008 – How to Compare Current Row with Previous or Next Row

Related Question