Additional column within subselect query

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 2005 Forums

Transact-SQL (2005)

Additional column within subselect query

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 05:31:14

I have an existing query:

SELECT	a.debtid,
		b.debtorid,
		a.code,
		a.originalsum,
		b.debtorno,
		b.amountpaidtodate,
		a.originalsum - SUM(b.amountpaidtodate) OVER (PARTITION BY a.debtid) AS balance, 
        (select min(convert(varchar,p.paymentdate,105)) from payment AS p where p.debtorID = b.debtorid) AS lastpayment

FROM		debt AS a
JOIN	debtor AS b ON b.debtID = a.debtID

but I also need to return an additional column 'amount' within the subselect so the subselect returns both lastpayment AND amount columns.
How can do this please?

I guess what I am asking is for the additional column 'amount' (from table 'payment') associated with the column 'lastpayment' produced via the subselect.

Hope that explains things better. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 06:11:16

You can use CROSS APPLY, or OUTER APPLY for this.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 06:13:36

[code]SELECT a.debtid,
b.debtorid,
a.code,
a.originalsum,
b.debtorno,
b.amountpaidtodate,
a.originalsum - SUM(b.amountpaidtodate) OVER (PARTITION BY a.debtid) AS balance,
f.LastPayment,
f.payment
FROM debt AS a
INNER JOIN debtor AS b ON b.debtID = a.debtID
OUTER APPLY (
select min(convert(varchar, p.paymentdate,105)) AS lastpayment,
SUM(payment) AS payment
from payment AS p
where p.debtorID = b.debtorid
) AS f[/code]

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 06:43:14

Many thanks for that Peso.

However, I have just discovered that if the debt has, say, two debtors associated with it, the date column returned in the subselect is correct for the second debtor, but incorrect for the first.

Anyone know why this is please? I'm stumped!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 06:44:12

Please post some relevant sample data and your expected output.

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 07:08:14

Well, if I have a table


payment
-------

debtorid  date        amount
--------  ----        ------
123       01/04/2008  5.00
101       12/04/2008  12.00
123       23/04/2008  11.00
123       02/05/2008  10.00
101       10/05/2008  10.00

I want to return the most recent payment amount made and the date of that payment to give:


debtorid  date       amount
--------  ----       ------
123       02/05/2008 10.00
101       10/05/2008 10.00

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-29 : 07:18:34

declare @t table (debtorid int,date datetime, amount decimal(18,2))
insert into @t select
123, '04/01/2008', 5.00 union all select
101 , '04/12/2008', 12.00 union all select
123 , '04/23/2008', 11.00 union all select
123 , '05/02/2008', 10.00 union all select
101 , '05/10/2008 ', 10.00

select t.debtorid,t.date,amount
from @t s
inner join (
select debtorid,max(date)as date from @t group by debtorid)t on t.debtorid = s.debtorid and t.date = s.date

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 07:49:47

Woukld some kind soul be good anough to examplain this please?


select t.debtorid,t.date,amount
from @t s
inner join (
select debtorid,max(date)as date from @t group by debtorid)t on t.debtorid = s.debtorid and t.date = s.date

is this statment creating a JOIN with itself? I'm confused here.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-29 : 07:52:03

In this query u will get the
debtroid value and max date values (select debtorid,max(date)as date from @t group by debtorid)
and then i had join with same table to get the amount for that id on that date

U got the required Output?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-29 : 07:58:38

[code]
from @t s
inner join (

[/code]
is there a typo here?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-29 : 08:11:36

You can read (or write) this also as:
...
from @t as s
inner join(...

The tablevar @t is getting the aliasname s

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 12:16:31

quote:
Originally posted by OldMySQLUser

Well, if I have a table
payment
-------

debtorid date amount
-------- ---- ------
123 01/04/2008 5.00
101 12/04/2008 12.00
123 23/04/2008 11.00
123 02/05/2008 10.00
101 10/05/2008 10.00

I want to return the most recent payment amount made and the date of that payment to give:
debtorid date amount
-------- ---- ------
123 02/05/2008 10.00
101 10/05/2008 10.00

SELECT debtorid, date, amount
from (
select debtorid, date, amount, row_number() over (partition by debtorid order by date desc) as recid
from table1
) as f
where recid = 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources