0

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

4

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;
| improve this answer | |
  • thanks for replay. Is the code will be like this use WaterProject WITH x AS ( SELECT CusAdd.CusID, rn = ROW_NUMBER() OVER (PARTITION BY c.CusID ORDER BY ct.MonthBill DESC), CusAdd.CusID, -- rest -- Calculte_Table.MonthtBill FROM Calculte_Table INNER JOIN CusAdd ON Calculte_Table.CusID = CusAdd.CusID ) SELECT CusAdd.CusID --rest -- FROM Calculte_Table , CusAdd WHERE rn = 1 ORDER BY Calculte_Table.MonthtBill DESC – AzzaM Jan 8 '17 at 17:05
  • in this code there is error near with x . ` the error message Msg 319, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.` – AzzaM Jan 8 '17 at 17:07
  • @AzzaM This is exactly why I included a leading semi-colon: ;WITH not WITH. – Aaron Bertrand Jan 8 '17 at 17:18
  • 1
    ♦ I notcied that after I post the comment. In Line 2 witch is ;with x as there is an error appears to me , the Error Message is The column 'CusID' was specified multiple times for 'x'. – AzzaM Jan 8 '17 at 17:28
  • 1
    @AzzaM well, did you add CusID a second time??? I mean, seriously... you have to be capable of doing a little bit of troubleshooting your own code. – Aaron Bertrand Jan 8 '17 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.