Sub Query? - SQL Server Forums

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

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

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

Bazza
Starting Member

3 Posts

Posted - 2015-05-05 : 04:43:30

Thanks very much.

I'll read the posting guidelines!

Thanks

Lee

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources