Skip to Content
0

SQL: Return value from a different row

Dec 16, 2016 at 08:48 AM

46

avatar image

Hi all,

I want to return a value from a different row in ojdt

this is the original table

this should be the new output

New Output

im trying to join JDT1 and OCRD table with CardCode as key, but the column VatGroup, BaseSum and VatAmount are returing Null values

thanks

--this is my query


select 
	T0.Number,
	T1.VatGroup,
	isnull(T1.BaseSum,0) as [BaseSum],
	T0.BaseRef,
	isnull(T1.VatAmount,0) as [VatAmount],
	T1.ShortName,
	T0.RefDate
from OJDT T0
inner join JDT1 T1
on T0.TransId = T1.TransId
--inner join OCRD T2
--on T2.CardCode = T1.ShortName
where T0.Number = T0.Baseref
and T1.VatGroup is not null
and T0.Number = '510038635'


output.png (7.0 kB)
capture.png (3.2 kB)
output.png (7.0 kB)
capture.png (3.2 kB)
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Dec 19, 2016 at 05:43 PM
1

Hi Jon,

I believe this query will return what you want:

SELECT 
	T0.Number,
	MAX(T1.VatGroup),
	SUM(ISNULL(T1.BaseSum,0)) as [BaseSum],
	T0.BaseRef,
	SUM(ISNULL(T1.VatAmount,0)) as [VatAmount],
	MAX(T1.ShortName),
	MAX(T0.RefDate)
FROM
	OJDT T0
	INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
WHERE 
	T0.Number = T0.Baseref 
	AND T1.VatGroup IS NOT NULL 
	AND T0.Number = '510038635'
GROUP BY
	T0.Number, T0.BaseRef

Hope it helps.

Kind Regards,

Diego Lother

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Diego. This is very helpful.

0
Gonzalo Gomez Dec 16, 2016 at 10:06 AM
1

Insert T1.ContraAct in your query

Share
10 |10000 characters needed characters left characters exceeded