Skip to Content

SQL: Return value from a different row

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 19, 2016 at 05:43 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 16, 2016 at 10:06 AM

    Insert T1.ContraAct in your query

    Add comment
    10|10000 characters needed characters exceeded