SQL: Return value from a different row

[Former Member]

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

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'

[former_member185682 · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Diego. This is very helpful.

[gonzalogomez]

Insert T1.ContraAct in your query