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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sub Query?

Author  Topic 

Bazza
Starting Member

3 Posts

Posted - 2015-05-01 : 09:36:22
Hi,

I'm fairly new to T-SQL and am struggling a bit with this one....

I have two tables Customer and Payments.

What I need is to show the last 4 payments for each customer which sounds really easy but I'm can't seem to get it to work. Here's what I have (don't laugh!)

select a.custid,
a.name,
b.trandate,
b.appliedamt
from customer a
left
join cashhead b
on a.company = b.company
and a.custid = b.custid
where a.company = 'SF06'
and b.trandate in (select top 4 trandate
from cashhead u
where u.company = b.company
and u.custid = b.custid
and u.trantype = 'PayInv'
order
by u.trandate desc
)
order
by b.trandate desc


This sort of works but doesn't just return 4 records if the customer has multiple payments on the same date. I don't have anything which is unique to be able to just get the last 4 payments. Is there another way of doing this?

Thanks

Lee

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-01 : 09:50:50
Try this:


SELECT custid
,NAME
,trandate
,appliedamt
FROM (
SELECT a.custid
,a.NAME
,b.trandate
,b.appliedamt
,rn = row_number() OVER (
ORDER BY b.trandate DESC
)
FROM customer a
LEFT JOIN cashhead b ON a.company = b.company
AND a.custid = b.custid
WHERE a.company = 'SF06'
AND b.trandate IN (
SELECT TOP 4 trandate
FROM cashhead u
WHERE u.company = b.company
AND u.custid = b.custid
AND u.trantype = 'PayInv'
)
)
WHERE rn <= 4
Go to Top of Page

Bazza
Starting Member

3 Posts

Posted - 2015-05-01 : 10:18:20
Hi,

Thanks for the quick reply.

I get the following error when trying the SQL....

Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'WHERE'.

Thanks

Lee
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-01 : 11:34:03
You need to alias the subquery. So just before the last "WHERE":


) subq
WHERE cn <= 4


btw, if you follow these posting guidelines: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

it's easier for us to set up a test environment to catch the little things
Go to Top of Page

Bazza
Starting Member

3 Posts

Posted - 2015-05-05 : 04:43:30
Thanks very much.

I'll read the posting guidelines!

Thanks

Lee
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-05-05 : 17:06:44
OUTER APPLY was designed for this type of situation:

select a.custid,
a.name,
b.trandate,
b.appliedamt
from customer a
outer apply (
select top (4) trandate, appliedamt --,<other_column(s)_if_wanted>
from cashhead u
where u.company = a.company
and u.custid = a.custid
and u.trantype = 'PayInv'
order
by u.trandate desc
) as b
where a.company = 'SF06'
order
by b.trandate desc, a.custid

Go to Top of Page
   

- Advertisement -