Nulling Values in SQL Server

Question

Today's issue consists of nulling a specific value when the number in the vehicle column changes, for example, from 1087 to 1088. The reason is so that the miles traveled column will calculate appropriately. If you look at the picture below, once the result set goes from one vehicle to another, the previous odometer reading is pulled that is for the previous vehicle. To fix this issue, I want to make this cell changed to 0 or null, but only when the vehicle changes. Is this possible?

Edit: I apologize for not being thorough in my description. The challenging part is that I cannot use another query to manipulate the data, all of the changes must be made in the query that produced this table AND it must be done for the previous hour column as well. I apologize for everyone who has already attempted to help! I can provide the query if needed!

DECLARE
        @rptStartDate date,
        @rptEndDate date

Set @rptStartDate = '11/1/2018'
Set @rptEndDate = '11/30/2018'


Select  e.AccountingCode As [Vehicle], CONCAT(e.year + ' ', e.make + ' ', e.model + ' ', CAST(e.[Description] as VARCHAR(80))) As Name, 
        IIF(cff.StringType = '', cff1.StringType, cff.StringType) as 'Operator', fta.Fluid, so.Odometer, LAG(so.Odometer) OVER (ORDER BY e.AccountingCode) 'Previous Odometer', so.Odometer - LAG(so.Odometer) OVER (ORDER BY e.AccountingCode) 'Miles Traveled',
        sh.Hour, LAG(sh.Hour) OVER (ORDER BY e.AccountingCode) 'Previous Hours', sh.Hour - LAG(sh.Hour) OVER (ORDER BY e.AccountingCode) 'Hours Used' ,fta.FIFOTotalCost/fta.TotalGallons as [Avg Price], 
        fta.TotalGallons As [Total Gallons], fta.FIFOTotalCost As [Total Cost]
from Equipment as e left outer join

    (SELECT     cf.CustomFieldID, cf.EntityID, cf.StringType, cfc.FieldName, cfc.CategoryType, cfc.EntityName
     FROM       CustomField AS cf INNER JOIN
                CustomField_Category AS cfc ON cf.CustomField_CategoryID = cfc.CustomField_CategoryID
     WHERE cfc.FieldName = 'Operator' and cf.IsDeleted = 0) as cff on e.EquipmentID = cff.EntityID left outer join

    (SELECT     cf.CustomFieldID, cf.EntityID, cf.StringType, cfc.FieldName, cfc.CategoryType, cfc.EntityName
     FROM       CustomField AS cf INNER JOIN
                CustomField_Category AS cfc ON cf.CustomField_CategoryID = cfc.CustomField_CategoryID
     WHERE cfc.FieldName = 'Unassigned Description' and cf.IsDeleted = 0) as cff1 on e.EquipmentID = cff1.EntityID left outer join

     -- The first and last hour meter reading value after the start date for each piece of equipment
    (Select emh.Equipment_EquipmentID, emh.TrueReading as 'Hour'
     from Equipment_MeterHistory as emh
     group by emh.Equipment_EquipmentID, emh.TrueReading) as sh on e.EquipmentID = sh.Equipment_EquipmentID left outer join

     -- The first and last odometer reading value after the start date for each piece of equipment
    (Select emh.Equipment_EquipmentID, emh.TrueReading as 'Odometer'
     from Equipment_OdometerHistory as emh

     group by emh.Equipment_EquipmentID, emh.TrueReading) as so on e.EquipmentID = so.Equipment_EquipmentID left outer join

     -- Fuel total gallons and cost per container per fluid
    (SELECT  fcD.EquipmentID, fcD.FluidID as 'FluidID', MAX(f.code) as 'Fluid', 
        SUM(CASE WHEN ft.DestinationContainerID = ftd.DestinationContainerID THEN fti.Quantity ELSE -1* fti.Quantity END) as 'TotalGallons', 
        SUM(fifo.Cost * CASE WHEN ft.DestinationContainerID = ftd.DestinationContainerID THEN fti.Quantity  ELSE -1* fti.Quantity END ) as 'FIFOTotalCost'   
     FROM        FluidTransfer AS ft left outer join
            fluidtransferdetail as ftd on ft.ID = ftd.FluidTransferID  and (ft.DestinationContainerID = ftd.DestinationContainerID or ft.DestinationContainerID = ftd.SourceContainerID)left outer join
            FluidTransferInventory as fti on ftd.id = fti.FluidTransferDetailID left outer Join
            FluidContainer as fcD on ft.DestinationContainerID = fcD.ID inner join
            fluid as f on ft.fluidid = f.ID inner join
            FluidCost as fifo on fti.FifoCostID = fifo.ID
     where ftd.Type in (0,1) and (isburntank = 1)
     Group BY fcd.EquipmentID, fcD.FluidID) as fta on e.id = fta.EquipmentID left outer join

     --Count of transactions by equipment and fluid
    (SELECT fcd.EquipmentID, ft.FluidID, COUNT(*) as 'TransactionCount'
     FROM   FluidTransfer AS ft left outer join
            FluidContainer as FcD on ft.destinationcontainerid = fcD.id
     where(isburntank = 1)
     Group By fcd.EquipmentID, ft.FluidID) as ftc on fta.EquipmentID = ftc.EquipmentID and fta.FluidID = ftc.FluidID
WHERE (((e.AccountingCode) >= '0900' and (e.AccountingCode) <= '9999') AND ((e.IsDeleted)=0)) AND fta.FIFOTotalCost > 0

order by AccountingCode

UPDATE: Here is the select statement now that I have made changes.

Select  CAST(e.AccountingCode as NVARCHAR(80)) As [Vehicle], CONCAT(e.year + ' ', e.make + ' ', e.model + ' ', CAST(e.[Description] as VARCHAR(80))) As Name, 
    IIF(cff.StringType = '', cff1.StringType, cff.StringType) as 'Operator', fta.Fluid, so.Odometer,  CASE WHEN COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer),0) <> e.AccountingCode 
    THEN 0 ELSE LAG(so.Odometer) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer)
    END as 'Previous Odometer', so.Odometer - IIF((CASE WHEN COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer),0) <> e.AccountingCode THEN 0 ELSE LAG(so.Odometer) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer) END) = 0  , 
    so.Odometer,CASE WHEN COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer),0) <> e.AccountingCode THEN 0 ELSE LAG(so.Odometer) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer) END ) as 'Miles Traveled',
    sh.Hour, CASE WHEN COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, sh.Hour),0) <> e.AccountingCode 
    THEN 0 ELSE LAG(sh.Hour) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, sh.Hour)
    END as 'Previous Hours', sh.Hour - IIF((CASE WHEN COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, sh.Hour),0) <> e.AccountingCode THEN 0 ELSE LAG(sh.Hour) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, sh.Hour) END) = 0  ,
    sh.Hour,CASE WHEN COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, sh.Hour),0) <> e.AccountingCode THEN 0 ELSE LAG(sh.Hour) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, sh.Hour) END ) as 'Hours Used' ,fta.FIFOTotalCost/fta.TotalGallons as [Avg Price], 
    fta.TotalGallons As [Total Gallons], fta.FIFOTotalCost As [Total Cost]

I know this is probably unpleasant on the eyes, and could use some reformatting, but for now I am just happy that it works!! Thanks everyone for the help!

Answer 1

I assume that Vehicle + Odometer set the correct order.

You can flag the record where Vehicle changes in this way:

COALESCE(LAG(Vehicle) OVER (PARTITION BY Vehicle ORDER BY Vehicle, Odometer),0) <> Vehicle THEN 1 ELSE 0 END

Note: I've used COALESCE(LAG(), 0). If there is some Vehicle=0 you should change this value.

;WITH ct AS
(
    SELECT
        Vehicle,
        Odometer,
        [Previous Odometer],
        CASE WHEN COALESCE(LAG(Vehicle) OVER (PARTITION BY Vehicle ORDER BY Vehicle, Odometer),0) <> Vehicle THEN 1 ELSE 0 END as Modif
    FROM
        YourTable
)
UPDATE t1
SET 
    [Previous Odometer] = 0
FROM
   YourTable t1
JOIN
   ct
   ON ct.Vehicle = t1.Vehicle
   AND ct.Odometer t1.Odometer
WHERE
   Modif = 1;

Update

According your recently added query try by changing:

LAG(so.Odometer) OVER (ORDER BY e.AccountingCode) 'Previous Odometer'

by

CASE WHEN 
COALESCE(LAG(e.AccountingCode) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer),0) <> e.AccountingCode 
THEN 0 
ELSE LAG(so.Odometer) OVER (PARTITION BY e.AccountingCode ORDER BY e.AccountingCode, so.Odometer)
END as 'Previous Odometer'

Keep in mind you're ordering by AccountingCode only, you need another column, usually this measures has been taken at some date or there is some identity column in this table that you should use in your ORDER clause.

I've used Odometer because I consider it always increases.

Answer 2

I presume these are query results. As such, you must have a current definition for your [Previous Odometer] column that is referencing the prior row. If so, then, logically speaking, what you need for this column definition is simply:

case
    when "prior_row".vehicle <> vehicle then null -- 'vehicle' = "current_row".vehicle
    else "existing_definition_of_column"
    end as [Previous Odometer]

So, more concretely:

case
    when lag(vehicle, 1, -1) over (order by vehicle, odometer) <> vehicle then null 
    else lag(odometer, 1, null) over (order by vehicle, odometer)
    end as [Previous Odometer]

The lag function here is saying, in the first case, grab the prior 1 (2nd argument) vehicle value (1st argument), when ordered by vehicle then odometer (the over clause) or if there is no prior row, use the value -1 (3rd argument).

In our case statement we are then comparing the obtained vehicle value with the current row's vehicle value. We use a default value of -1 in the scenario where there is no prior row, rather than null, as our comparison between null and an integer value would fail.

If the two values are different (which is the case you're asking about), then we set the [Previous Odometer] value to null, as you requested.

If they are not different - this is our else clause - we use the lag function similarly again - but this time to get the prior row's [Odometer] value, and in the scenario where there is no prior row, this time we do want null - because that's the [Previous Odometer] value you wanted to see whenever the "vehicle changed" (from row to row).