T-SQL How to Pull in One Record for Each Instance of Two of the Same Fields

Hello:

Below is my T-SQL code and attached is a sample result set.

I want to modify the code to show only one record for each instance of ITEMNUMBER and TRXLOCTN where the QTYREMAINING is the same.

In other words, I want the attached result set to show only one record--not two--since both records have the same ITEMNUMBER, TRXLOCTN, and QTYREMAINING.  

The QTYREMAINING is the next to the last column, while the ITEMNUMBER is the first column.  TRXLOCTN is the sixth column.

Please tell me how I can do this.

Thank you!

TBSupport



SELECT IV00101.ITEMNMBR AS ITEMNUMBER, IV00101.USCATVLS_1 AS [USE], IV00101.UOMSCHDL,
            IV00101.ITEMDESC AS ITEMNAME,
            --IV10200.DATERECD AS DATERECEIVED,
            --IV10200.QTYRECVD AS QTYRECEIVED,
            --IV10200.QTYSOLD AS QTYSOLD,
            --IV00101.ITMCLSCD AS ITEMCLASS,
            IV10200.UNITCOST AS UNITCOST,
            --IV00101.USCATVLS_1 as USE,
            --IV10200.PCHSRCTY AS RECEIPTTYPE,
            --IV10200.RCPTNMBR AS RECEIPTNO,
            IV10200.TRXLOCTN AS LOCATION,
            --( IV10200.QTYRECVD - IV10200.QTYSOLD ) AS QTYREMAINING,
                        SUM(IV10200.QTYRECVD - IV10200.QTYSOLD ) OVER (PARTITION BY IV10200.ITEMNMBR, IV10200.TRXLOCTN) As [QTYREMAINING],
            --( ( IV10200.QTYRECVD - IV10200.QTYSOLD ) * IV10200.UNITCOST ) AS CURRENTVALUE,
            --DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) AS AGEDDAYS,
            CASE
                 WHEN (
                        DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) < 60
                      ) THEN '60 Days'    
                 WHEN ( DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) < 90
                      ) THEN '90 Days'    
                 ELSE '120 Days'    
                 END  
            AS BUCKET
    FROM    ( DTZ..IV00101
              INNER JOIN DTZ..IV10200
                ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
            )
            INNER JOIN DTZ..IV00102
                ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
    WHERE   IV00102.RCRDTYPE = 1
            AND IV10200.PCHSRCTY <> 0
                        AND ( IV10200.QTYRECVD - IV10200.QTYSOLD ) > 0
                              AND IV00101.USCATVLS_1 IN ('FG', 'RAW')
                                    and IV00102.QTYONHND > 0
Results.docx
LVL 1
TBSupportAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
Have you tried adding DISTINCT after SELECT? BTW, the reason for duplication is caused probably by a non-unique condition in one of your INNER JOINs. It is very difficult for us to figure out what causes the duplication. However, you will be able to spot it very easily. Add ", *" right before the FROM word in your statement. This will bring all the columns from all tables. When you analyse the data you would probably notice that duplicating records will be different by a few columns (e.g. a table may contain records for the same ITEMNMBR but with different TYPE). By further restricting the filter conditions in your ON and WHERE clauses you will be able to achieve the distinct values without DISTINCT keyword.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>.. show only one record for each instance of ITEMNUMBER and TRXLOCTN where the QTYREMAINING is the same.

Without touching the above T-SQL, as my eyes kind of glaze over after ten lines ...

If two rows that have the same values for the above three columns, but different values in the other columns you want in the return set, what's the logic for choosing which row is returned, and which one isn't?
0
 
David ToddSenior DBACommented:
Hi,

The problem is I think that you've not used grouping correctly.

The sum clause is (I'm assuming) correct, but by not grouping the results you are ending up with multiple lines in your results.

To simplify things down completely, can you try this and report if the results are about right. If this minimal select is correct, then you can easily add the where clauses and joins to the other tables.

Regards
  David

PS some of your columns names are hard to read, and the indenting is inconsistent, all of which makes it fairly hard to read. Hard to read == hard to understand, hard to understand == likely to be buggy == hard to maintain. I've taken the liberty to post your reformatted code, which I think makes it easier to read. I've also dropped some possibly extraneous brackets. Its good practice to specify the schema (dbo I'm assuming) and use the database and so not have to specify the database each time.

select
	c.ItemNbr
	, c.trxloctn
	, sum( c.qtyRecvd - c.qtySold ) as qtyRemaining
from dbo.IV00101 c
group by
	c.ItemNbr
	, c.trxloctn
;

Open in new window

-- reformatted code
use dtz
go

SELECT 
	IV00101.ITEMNMBR AS ITEMNUMBER
	, IV00101.USCATVLS_1 AS [USE]
	, IV00101.UOMSCHDL
	, IV00101.ITEMDESC AS ITEMNAME
	, IV10200.UNITCOST AS UNITCOST
	, IV10200.TRXLOCTN AS LOCATION
	, SUM(IV10200.QTYRECVD - IV10200.QTYSOLD ) OVER (PARTITION BY IV10200.ITEMNMBR, IV10200.TRXLOCTN) As [QTYREMAINING]
	, CASE 
		WHEN DATEDIFF( DAY, IV10200.DATERECD, GETDATE()) < 60 THEN '60 Days'     
		WHEN DATEDIFF(DAY, IV10200.DATERECD, GETDATE()) < 90 THEN '90 Days'     
		ELSE '120 Days'    
	END AS BUCKET
FROM dbo.IV00101 
INNER JOIN dbo.IV10200 
	ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
INNER JOIN dbo.IV00102
	ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE   
	IV00102.RCRDTYPE = 1
	AND IV10200.PCHSRCTY <> 0
	AND ( IV10200.QTYRECVD - IV10200.QTYSOLD ) > 0
	AND IV00101.USCATVLS_1 IN ('FG', 'RAW')
	and IV00102.QTYONHND > 0 
;

Open in new window

0
 
TBSupportAuthor Commented:
I resolved this by placing the T-SQL code into a view and ran a "select DISTINCT" statement.  

Thanks, chaau!

TBSupport
0
 
Jeff_KingstonCommented:
I have yet to figure why after 45 years of every software company in history developing editors for all their languages to enforce uniformity, legibility, and general standardized presentation. SQL is still allowing third grader with crayon structure????
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.