How to display itemcode from jdt1

298 Views

Hi all,

I'm attempting to create a report that displays ItemCode andLineTotal from the iINV1 table grouped by the account field stored in JDT1 table. My issue is, if I total up the credits/debits from the JTD1 table, everything adds up correctly. When I add a field from the INV1 fields, all totals become inflated or just plan wrong. Is my below select startement incorrect? I understand lines will duplicate, but even running totals turn up with the wrong totals.. Thanks for your time.

SELECT DISTINCT "JDT1"."Account", "JDT1"."Credit", "JDT1"."RefDate", "JDT1"."Ref1", "OINV"."Ref1", "OINV"."DocEntry", "INV1"."DocEntry", "INV1"."LineTotal", "INV1"."ItemCode"

FROM ("Live"."dbo"."JDT1" "JDT1" INNER JOIN "Live"."dbo"."OINV" "OINV" ON "JDT1"."TransId"="OINV"."TransId") INNER JOIN "Live"."dbo"."INV1" "INV1" ON "OINV"."DocEntry"="INV1"."DocEntry"

WHERE "JDT1"."RefDate">={ts '2010-08-01 00:00:00'} AND "JDT1"."Account"=N'400014'

ORDER BY "JDT1"."Account"