0

I have three tables that contains data as below:

Users

Id   Name     Other_Columns
---------------------------
1    John     Blah
2    Ricky    Blah
3    Stella   Blah
4    Bob      Blah

Saldo

Id   User_id   Saldo
--------------------
1    3         0.00
2    1         9.00
3    2         0.15
4    4         3.50

Payments

Id   User_id   Amount     Paid_date
------------------------------------------
1    2         10.00      2014-09-01 08:10
2    2         25.00      2014-09-01 09:00
3    3         100.00     2014-05-10 12:47
4    1         20.50      2014-02-23 15:30

How to get result like this:

Id   Name     Saldo     Last Payment
------------------------------------------
1    John     9.00      23.02.2014 20.50
2    Ricky    0.15      01.09.2014 25.00
3    Stella   0.00      0000-00-00 0.00
4    Bob      3.50      10.05.2014 100.00

Thank you.

1
  • @a_horse_with_no_name, XAMPP (php & mysql)
    – bofanda
    Sep 13, 2014 at 21:26

1 Answer 1

1
;with [Users](Id,Name,Other_Columns)
AS(
    SELECT 1,'John','Blah' UNION ALL
    SELECT 2,'Ricky','Blah' UNION ALL
    SELECT 3,'Stella','Blah' UNION ALL
    SELECT 4, 'Bob','Blah')
,
Saldo (Id,[User_id],Saldo)
AS
    (SELECT 1,    3 ,       0.00 UNION ALL
    SELECT 2,    1,        9.00 UNION ALL
    SELECT 3,    2,         0.15 UNION ALL
    SELECT 4,    4,         3.50 )

,Payments(Id,[User_id],Amount,Paid_date)
AS
    (SELECT 1,2,10.00,'2014-09-01 08:10' UNION ALL
    SELECT 2,2,25.00,'2014-09-01 09:00' UNION ALL
    SELECT 3,4,100.00,'2014-05-10 12:47' UNION ALL
    SELECT 4,1,20.50,'2014-02-23 15:30')


SELECT
    ID
    ,Name
    ,Saldo
    ,[Last Payment]
FROM
(
    SELECT
        U.ID,U.Name,S.Saldo 
        ,LEFT(COALESCE(P.Paid_date,'0000-00-00'),10)  + ' ' + CAST( COALESCE(Amount,0) AS VARCHAR(50))  [Last Payment]
        ,ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY COALESCE(P.Paid_date,'0000-00-00') DESC) AS RN
        FROM
            [Users] AS U
            INNER JOIN Saldo AS S
            ON U.ID= S.[User_id]
            left JOIN Payments AS P
            ON P.[User_id] = U.ID
)X
WHERE X.RN =1

output:

ID  Name    Saldo   Last Payment
1   John    9.00    2014-02-23 20.50
2   Ricky   0.15    2014-09-01 25.00
3   Stella  0.00    0000-00-00 0.00
4   Bob 3.50    2014-05-10 100.00
1
  • 2
    You should mention that your solution only works on SQL Server (due to some non-standard syntax elements)
    – user1822
    Sep 8, 2014 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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