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,203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 25

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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