Using a subquery to return as a field

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

Using a subquery to return as a field

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-21 : 21:17:29

For the query below, I need to get a specific field from the sub query. need the amount to return for the Max(transaction_date) of each id as a separate field in the query.

SELECT distinct N.ID
,NS.WEB_LOGIN
,(select amount from (select Max(transaction_date)
from activity where activity.id = n.id group by id)
From Name as N
left join activity on n.id = activity.id
Where
N.Member_Type in ('BIRTH','HON')
And N.Status in ('A','IA')

Order By N.ID ASC

Thanks,
Dz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-21 : 21:29:59

[code]
SELECT *
FROM
(
SELECT N.ID
,N.WEB_LOGIN
,A.AMOUNT
,ROW_NUMBER() OVER (PARTITION BY N.ID ORDER BY A.TRANSACTION_DATE DESC) AS RN
FROM [NAME] as N
LEFT JOIN ACTIVITY as A ON N.ID = A.ID
WHERE N.MEMBER_TYPE iN ('BIRTH', 'HON')
AND N.STATUS IN ('A', 'IA')
) B
WHERE B.RN = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 10:09:12

Error: Msg 195, Level 15, State 10, Line 7
'ROW_NUMBER' is not a recognized function name.

Also where is my max date coming from?

The N.id and Web_login are two fields from different tables which I join later on. How do i get ",(select amount from (select Max(transaction_date)
from activity where activity.id = n.id group by id)
From Name as N
left join activity on n.id = activity.id
Where
N.Member_Type in ('BIRTH','HON')
And N.Status in ('A','IA')"
to return a different field than what is being grouped?

Example:

ID Date Amount Type
101 01/01/2001 42.00 DUES
101 03/01/2012 101.63 DUES
101 01/13/2007 23.16 ORDER

I need it to return the amount only for the max date for all DUES types.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 10:13:17

what version of SQL Server are you using ?

KH
[spoiler]Time is always against us[/spoiler]

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 10:20:32

2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-22 : 10:21:40

And your compatibility level is ... ?

N 56°04'39.26"
E 12°55'05.63"

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 10:32:02

2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 10:42:00

are you able to switch to comp level 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 10:44:48

No - too many other dbs to worry about. I got it a little closer (no errors), but it is only returning NULLs for the amount. All other fields are correct.

SELECT distinct N.ID
,NS.WEB_LOGIN
,(select amount where seqn in (select Max(transaction_date)
from activity where activity.id = n.id group by id)) as MaxDateAmount
From Name as N
left join activity on n.id = activity.id
LEFT JOIN Name_Address as NA On N.ID = NA.ID AND NA.Purpose = ('Work')
Where
N.Member_Type in ('BIRTH','HON','MEM','NONM2','STU','PRECT')
And N.Status in ('A','IA')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 11:48:49

[code]
SELECT N.ID
,N.WEB_LOGIN
,A.AMOUNT
FROM [NAME] as N
LEFT JOIN ACTIVITY as A ON N.ID = A.ID
LEFT JOIN
(
SELECT ID, MAX(TRANSACTION_DATE) as TRANSACTION_DATE
FROM ACTIVITY
GROUP BY ID
) as M ON N.ID = M.ID
AND A.TRANSACTION_DATE = M.TRANSACTION_DATE
WHERE N.MEMBER_TYPE iN ('BIRTH', 'HON')
AND N.STATUS IN ('A', 'IA')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 11:57:41

I don't Understand this, or how it even Works

(select amount where seqn in (
select Max(transaction_date) from activity where activity.id = n.id group by id)
) as MaxDateAmount

I don't think that would even PARSE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 11:59:43

That pulls back all amounts from the activities rather than to match teh amounts to the transaction with the max date.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 12:11:08

I find it hard to believe that actually runs. Please post the DDL for your tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 12:17:33

It does, however it give me NULLs. I started with this:
(select Max(transaction_date)
from activity where activity.id = n.id group by id) as MaxDate as a sub query to create the values in the MaxDate field. It returns:

ID WebLogin MaxTransDate
91879 91879 2012-03-09 00:00:00.000
91899 91899 2012-03-09 00:00:00.000
91900 91900 2012-03-10 00:00:00.000
91902 91902 2012-03-10 00:00:00.000

The new script:
ID WebLogin MaxTransDateAmount
91879 91879 Null
91899 91899 Null
91900 91900 Null
91902 91902 Null

I need it to look like this:
ID WebLogin MaxTransDateAmount
91879 91879 25.00
91899 91899 30.00
91900 91900 80.00
91902 91902 13.00

dzabor
Posting Yak Master

138 Posts

Posted - 2012-03-22 : 14:59:33

I got it working. MNy script was correct, jut needed to use a max seqn number, the dates were throwing it off.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources