Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query

Author  Topic 

rowter
Yak Posting Veteran

76 Posts

Posted - 2014-07-25 : 10:02:43


I have a table 'Payments' with following data:

Firstname lastname Date AmountPaid
---------------------------------------
AARON MEDRANO 2014-01-01 96.00
AARON MEDRANO 2014-01-10 123.00
AARON MEDRANO 2014-02-12 36.00
AARON MEDRANO 2014-12-25 120.00
ABIGAIL CARRANZA 2014-12-01 100.00
ABIGAIL CARRANZA 2014-03-03 142.00
ABIGAIL CARRANZA 2014-12-25 125.00

I need only one records for each client with the minimum date.
I need to know when was the first time the client got paid in a given year.
So the output will be

Firstname lastname Date AmountPaid
---------------------------------------
AARON MEDRANO 2014-01-01 96.00
ABIGAIL CARRANZA 2014-03-03 142.00

Appreciate your help!!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 10:10:51
Assuming those are all the columns you really have you could do the following:

;with MyPayments
AS
(
SELECT Firstname,lastname,MIN([Date]) PayDate
FROM Payments
GROUP BY Firstname,lastname
)

SELECT * FROM Payments P
INNER JOIN MyPayments MP ON P.Firstname = Mp.Firstname,P.lastname = Mp.lastname AND P.[Date] = MP.PayDate


Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-25 : 10:37:20
Here are 3 alternatives (the last one, being the prefered one):
select a.*
from payments as a
inner join (select firstname
,lastname
,min([date]) as [date]
from payments
group by firstname
,lastname
) as b
on b.firstname=a.firstname
and b.lastname=a.lastname
and b.[date]=a.[date]
;
select a.*
from payments as a
left outer join payments as b
on b.firstname=a.firstname
and b.lastname=a.lastname
and b.[date]<a.[date]
where b.firstname is null
;
select *
from (select *
,row_number() over (partition by firstname,lastname order by [date]) as rn
from payments
) as a
where rn=1
;
Go to Top of Page

rowter
Yak Posting Veteran

76 Posts

Posted - 2014-07-25 : 11:01:49
Thanks Michael and bitsmed for giving me an idea on how to work on this query...
Go to Top of Page
   

- Advertisement -