How to Get Last Values in SQL Server 2008

sql server

I need to make a program that calculates money for water bills. Here are table definitions that I created:

CusAdd_Table 
    Id,
    Name,
    Phone,
    FamilyNo,
    City,
    SubscribeDate   

Calu_Table    
    NowData,
    PerviousData,
    Diff,
    MonthtBill,
    money, 
    id

The Diff column is computed as NowData-PerviousData. The id column is a reference to CusAdd_Table. The Money column is calculated with some differentiation. The MonthtBill column is a dataTimepicker used for ordering.

My table structure is able to easily add new subscribers to the database. When I add a new bill for a month I insert a row into the Calu_Table table. For example, I would specify the values for the NowData,PerviousData,[NowData-PerviousData] as Diff columns when inserting data for Jan. I would need to insert additional rows to insert data for Feb and Mar.

I want to print last month's data but the code needs to work without knowing the number of subscribers upfront. I tried the below code and it did not satisfy my requirements because I want every Id with its last value.

SELECT
CusAdd.CusID AS Expr1,
CusAdd.CusName, 
CusAdd.CusPhone,
CusAdd.CusFamily, 
CusAdd.CusCity,
CusAdd.CusSubscribe,
Calculte_Table.Months,
Calculte_Table.NowRead,
Calculte_Table.ExRead,
Calculte_Table.Esthlak,
Calculte_Table.WhatHePay,
Calculte_Table.Dis_Money,
Calculte_Table.MonthtBill
FROM Calculte_Table 
INNER JOIN CusAdd ON Calculte_Table.CusID = CusAdd.CusID
ORDER BY Calculte_Table.MonthtBill DESC;

Here is the result:

enter image description here

Best Answer

You can use a CTE (or subquery) to determine which row is the latest per customer, then filter for those in the outer query. Consider using more legible formatting (tabs and carriage returns are your friends), table aliases, and always use the schema prefix.

;WITH x AS 
(
  SELECT 
    c.CusID, 
    rn = ROW_NUMBER() OVER (PARTITION BY c.CusID ORDER BY ct.MonthBill DESC)
    c.CusName, 
      ... all those other columns ...
    ct.MonthtBill
  FROM
    dbo.Calculte_Table AS ct
  INNER JOIN
    dbo.CusAdd AS c
    ON ct.CusID = c.CusID
)
SELECT CusID, CusName, ... all those other columns ...
FROM x
WHERE rn = 1
ORDER BY MonthtBill DESC;
Related Question