Solved

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

Posted on 2014-01-29
5
1,149 Views
Last Modified: 2015-01-13
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
0
Comment
Question by:TBSupport
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39819861
>.. 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
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39819865
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39819952
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
 
LVL 1

Author Comment

by:TBSupport
ID: 39820635
I resolved this by placing the T-SQL code into a view and ran a "select DISTINCT" statement.  

Thanks, chaau!

TBSupport
0
 

Expert Comment

by:Jeff_Kingston
ID: 40546618
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now