Question :
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.
Answer :
;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