Using MAX of a field to select a record returns MAX of the table ignores the condition

Question

I'm doing a stored procedure for a report, and I'm trying to get only those records with the highest value of a determined field (accumulated amount), the thing is I can't seem to find the solution to this, the only solution that i've came up with is using an extra condition, the problem is the field changes every month (period) and not all the records are updated but I need to retrieve them all... (if an asset is depreciated there wont be anymore records relating that asset in that table)

I'm sorry if this is confusing, I'll try my best to explain

The report needs to have for each supplier registered a list of the assets that supplies, their description, their current location, it's price, and how much money still needs to be depreciated from the asset.

So, what I'm doing it's first getting the list of suppliers, then getting the list of assets associated with a location (Using cursors) then I try to calculate how much money needs to be depreciated, there's a table called 'DEPRECIACIONES' that stores the asset, the period, and how much money has been depreciated from that asset for each period and for each asset that hasn't been completely depreciated. The problem comes when I try to calculate the MAX amount of money depreciated for an asset and then selecting the row for that item that has that MAX amount, I'm sure i'm doing something wrong, my TSQL and general database knowledge is not good and I'm trying to learn by myself.

I've uploaded the schema, tables and the stored procedure that throws the wrong output here:

http://sqlfiddle.com/#!3/78c32

The Right output should be something like this:

Proveedor | Activo | Descripcion | Ubicacion Actual | Costo Adquisicion | Saldo sin depreciar | Periodo

Supplier | Asset | Description | Current Location | Cost | Money to be depreciated | Period
-------------------------------------------------------------------------------------------
Monse    |ActivoT|  texthere   |       1114       |2034.50|    RANDOM NUMBER HERE  |RandomP
Monse    |cesart |  texthere   |       4453       |4553.50|    RANDOM NUMBER HERE  |RandomP
nowlast  | activ |  texthere   |       4453       |1234.65|    RANDOM NUMBER HERE  |RandomP
nowlast  |augusto|  texthere   |       4450       |4553.50|    RANDOM NUMBER HERE  |RandomP
Sara     |Activo |  texthere   |       1206       |746.65 |    RANDOM NUMBER HERE  |RandomP

I'd really appreciate telling me what i'm doing wrong (which is probably a lot) and how to fix it, thank you in advance.

Answer 1

Good skills in giving complete information via SqlFiddle.

I don't have a complete answer for you, but this may help.

Firstly, ditch the cursor - it's hard to debug and possibly slow. Refactor to a SELECT statement. This is my attempt, which should be logically equivalent to your code:

SELECT 
  p.Proveedor,
  a.Activo, 
  a.Descripcion, 
  Ubi.Ubicacion, 
  saldo_sin_depreciar = a.Costo_adquisicion - d.Monto_acumulado,
  d.Periodo

FROM  
  PROVEEDORES p
    INNER JOIN ACTIVOS_FIJOS a  ON a.Proveedor = p.Proveedor
    INNER JOIN DEPRECIACIONES d ON a.Activo = d.Activo 
    INNER JOIN 
      (
        SELECT 
         MAX(d1.Monto_acumulado) AS MaxMonto 
        FROM  DEPRECIACIONES d1 
         INNER JOIN  DEPRECIACIONES d2 
           ON d1.Monto_acumulado = d2.Monto_acumulado
      ) MaxAe 
    ON d.Monto_acumulado = MaxAe.MaxMonto

    INNER JOIN ACTIVO_UBICACION Ubi ON a.activo = ubi.activo
    INNER JOIN   
      (
        SELECT 
         activo, 
         ubicacion, 
         Fecha_Ubicacion, 
         RowNum  = row_number() OVER ( partition BY activo ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate())))
        FROM  
          ACTIVO_UBICACION
      ) UbU 
    ON UbU.ubicacion = Ubi.Ubicacion 

WHERE 
    -- a.Activo IS NOT NULL AND
        UbU.RowNum = 1 

ORDER BY
  p.Proveedor

COMMENTS

I've moved the WHERE criteria that are defining the joins up into ON clauses in the table list, that makes it easier to see how you are joining the tables.

Note that all the joins are INNER, which may not be what you want - you may need some LEFT JOIN's, I don't understand the logic enough to say.

Also, in your cursor procedure the Ubi and UbU parts don't seem to explicitly join with the rest of the tables, so I've pencilled-in an INNER JOIN on the activo column, as this is the way the tables join in the FK relationship.

In your cursor code, you would effectively get a CROSS JOIN which is probably wrong and also expensive to run.

The WHERE clause a.Activo IS NOT NULL is not required, because the INNER JOIN ensures it.

Hope this helps you sort it out.