News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics GP (Archived) Eliminating duplicates...

Microsoft Dynamics GP (Archived)

Eliminating duplicates in SQL script

(0) Share

Report

Report

Posted on by Community Member Microsoft Employee

I've created a script to retrieve data from 4 tables: TX30000, SOP30200, SOP30300, and GL00100. I'm getting close to what I want but even with using 'select distinct' I'm getting duplicate rows. the below example displays data retrieved that represents one Invoice(22793) in the amount of 850.00 with two line items relating to two tax schedules with distinct distribution accounts. What I would like to retrieve is in the second section of information.

Data that is being retrieved:

Inv # TaxID DocAmt TaxAmt DistAcct

22793 LOCAL 850.00 19.12 4651

22793 LOCAL 850.00 19.12 4657

22793 STATE 850.00 59.50 4651

22793 STATE 850.00 59.50 4657

Data that I would like to retrieve:

22793 LOCAL 850.00 19.12 4657

22793 STATE 850.00 59.50 4651

Following is the script:

SELECT DISTINCT

TX30000.DOCDATE AS Document_Date, TX30000.CustomerVendor_ID AS Customer,

TX30000.DOCNUMBR AS Invoice_#, TX30000.TAXDTLID AS Tax_ID,

TX30000.DOCAMNT AS Document_Amount, TX30000.TAXABLE_AMOUNT AS Taxable_Amount,

TX30000.TAXAMNT AS Tax_Amount, SOP30200.FRTAMNT AS Freight_Amt,

SOP30300.LOCNCODE AS Site, GL00100.ACTNUMBR_1 AS Location,

GL00100.ACTNUMBR_2 AS Main_Account

FROM TX30000

LEFT JOIN SOP30300 ON TX30000.DOCNUMBR = SOP30300.SOPNUMBE

LEFT JOIN SOP30200 ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE

LEFT JOIN GL00100 ON GL00100.ACTINDX = SOP30300.SLSINDX

WHERE TX30000.DOCDATE > '2009-01-01'

Any help would be appreciated!

Debi

*This post is locked for comments

All responses Img

All responses (1)

Answers Img

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

Re: Eliminating duplicates in SQL script

Debi,

What I can initially see as the probable source of the duplication is the link between SOP30300 and TX30000:

LEFT JOIN SOP30300 ON TX30000.DOCNUMBR = SOP30300.SOPNUMBE

in which both can have multiple records for one SOP number.

So, if your document has 2 lines and 2 tax details, you will have 2x2=4 records.

I think the taxes are not related to the line level but the document level, so you may need to create two separate queries.

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2

Martin Dráb 230,198 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans