Solved

Issue on Join Query - SQL

Posted on 2011-03-06
12
267 Views
Last Modified: 2012-05-11
SQL 2008 / 2005

On executing  Table : TransactionReport -> Total # of rows affected is 671

After Joining with Plntrak PT : - Through Column : Rxnbr

I am getting 713 Rows.

Expected output is only 671. Where does this duplication occurs ? How to find these duplicate rows.
select		
			TR.[Last Name],
			TR.[First Name],
			TR.[Last 4 Digit of SSN],
			TR.[ZipCode],
			TR.[County],
			TR.[340B],
			TR.[DOB],
			TR.[Client ID],
			TR.[Insurance Status],
			TR.[PlanId],
			TR.[Generic Code],
			TR.[Drug Name],
			TR.[NDC],
			TR.[RxNbr],
			TR.[Fill Date],
			TR.[Dispensed Qty],
			TR.[Refills],
			TR.[Days Supplied],
			TR.[Plan Allows],
			TR.[Copay],
			TR.[Net due From Insurance],
			TR.[340B Price],
			TR.[340B Packet Size],
			TR.[340B Cost for this RX],
			TR.[Cardinal Price],
			TR.[Packet Size],
			TR.[Dispensing Fees]
			,PT.Rebill

from [TransactionReport] TR  
Left Join Plntrak PT  
on  PT.RxNbr  = CONVERT(Int, TR.RxNbr) 
and  PT.Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
AND   YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')

Open in new window

0
Comment
Question by:chokka
[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
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 3

Expert Comment

by:kraiven
ID: 35047455
The duplication is due to there being more than one row in Plntrak which matches a row on TransactionReport
e.g.
TR.RxNbr            PT.RxNbr
0                         0                   unique match
1                                              no match
2                         2                  
3                         2                   non-unique matches
                           2
                           3                   unique match

To find the duplicates you need to join the tables and do a count where the match is greater than 1

SELECT TR.RxNbr
FROM [TransactionReport] TR  
Left Join Plntrak PT  
on  PT.RxNbr  = CONVERT(Int, TR.RxNbr) 
and  PT.Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
AND   YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')
GROUP BY TR.RxNbr
HAVING COUNT(*) >1

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35047463
Use an inner join instead
Left join will get you all the records in the Report table

select		
			TR.[Last Name],
			TR.[First Name],
			TR.[Last 4 Digit of SSN],
			TR.[ZipCode],
			TR.[County],
			TR.[340B],
			TR.[DOB],
			TR.[Client ID],
			TR.[Insurance Status],
			TR.[PlanId],
			TR.[Generic Code],
			TR.[Drug Name],
			TR.[NDC],
			TR.[RxNbr],
			TR.[Fill Date],
			TR.[Dispensed Qty],
			TR.[Refills],
			TR.[Days Supplied],
			TR.[Plan Allows],
			TR.[Copay],
			TR.[Net due From Insurance],
			TR.[340B Price],
			TR.[340B Packet Size],
			TR.[340B Cost for this RX],
			TR.[Cardinal Price],
			TR.[Packet Size],
			TR.[Dispensing Fees]
			,PT.Rebill

from [TransactionReport] TR  
inner Join Plntrak PT on  PT.RxNbr  = CONVERT(Int, TR.RxNbr) 
and  PT.Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
AND   YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35051012
>>Use an inner join instead
Left join will get you all the records in the Report table<<
I am afraid that is not correct based on the definition of a LEFT JOIN.  Let me know if you want me to elaborate.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 35051413
Actually I misread what you stated.  But while it is true that it will return all the rows in the TransactionReport table, that is not the problem.  We know that there are 617 rows in that table.  The problem is that as suggested before (http:#a35047455) there is "more than one row in Plntrak which matches a row on TransactionReport"
0
 

Author Comment

by:chokka
ID: 35051834
ewangoya:- I am confused on your suggestion.

For the Table : TransactionReport we need " PT.Rebill " Value for all the RxNbr which is there in Transaction Report.  Suppose if Plntrak PT  doesnt have the matching RxNbr, we just need atleast NULL Value for the Column PT.Rebill.

So, i decided to go for Left Join.

If i use Inner Join - I believe only matching RxNbr rows will be fetched.

 
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 100 total points
ID: 35054085
As kraiven asked, do you have duplicates in Plntrak  table? If so, how do you want one record for RxNbr? Do you have any date field/ unique id to identify one record?
0
 
LVL 3

Assisted Solution

by:kraiven
kraiven earned 100 total points
ID: 35055077
chokka,

You asked how to find the duplicates originally - well the grouping select I posted will give you a list of all the rows in PlnTrak which have more than one row for a given RxNbr. In my solution I used you WHERE clause with its LEFT JOIN but an INNER JOIN would have sufficed.

SELECT TR.RxNbr
FROM [TransactionReport] TR  
INNER Join Plntrak PT  
on  PT.RxNbr  = CONVERT(Int, TR.RxNbr) 
and  PT.Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
AND   YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')
GROUP BY TR.RxNbr
HAVING COUNT(*) >1

Open in new window

0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 200 total points
ID: 35057107

@acperkins
You are right

My apologies, I misunderstood the question.

Now it all depends on what the author wants to do in case of multiple records in the Plntrak table
You can select a distinct record or top most record

Here is a suggestion to try
select		
			TR.[Last Name],
			TR.[First Name],
			TR.[Last 4 Digit of SSN],
			TR.[ZipCode],
			TR.[County],
			TR.[340B],
			TR.[DOB],
			TR.[Client ID],
			TR.[Insurance Status],
			TR.[PlanId],
			TR.[Generic Code],
			TR.[Drug Name],
			TR.[NDC],
			TR.[RxNbr],
			TR.[Fill Date],
			TR.[Dispensed Qty],
			TR.[Refills],
			TR.[Days Supplied],
			TR.[Plan Allows],
			TR.[Copay],
			TR.[Net due From Insurance],
			TR.[340B Price],
			TR.[340B Packet Size],
			TR.[340B Cost for this RX],
			TR.[Cardinal Price],
			TR.[Packet Size],
			TR.[Dispensing Fees]
			,PT.Rebill
from [TransactionReport] TR  
left Join (select distinct RxNbr
           from Plntrak 
           where  PT.Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
                  AND YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')) PT on  PT.RxNbr  = CONVERT(Int, TR.RxNbr)

Open in new window

0
 
LVL 3

Expert Comment

by:kraiven
ID: 35058042
ewangoya,

I'm not sure your SQL above would even execute. The outer SELECT requires PT.Rebill in it's return data but your PT has only RxNbr and you reference the "PT" alias, in the inner WHERE clause, before it is even defined.
Besides, I think chokka just wants to know why there are more than 671 rows returned, and how to find out what the extra rows are.

Perhaps some input from chokka would help?
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35058120
can you try this:

select          
                        TR.[Last Name],
                        TR.[First Name],
                        TR.[Last 4 Digit of SSN],
                        TR.[ZipCode],
                        TR.[County],
                        TR.[340B],
                        TR.[DOB],
                        TR.[Client ID],
                        TR.[Insurance Status],
                        TR.[PlanId],
                        TR.[Generic Code],
                        TR.[Drug Name],
                        TR.[NDC],
                        TR.[RxNbr],
                        TR.[Fill Date],
                        TR.[Dispensed Qty],
                        TR.[Refills],
                        TR.[Days Supplied],
                        TR.[Plan Allows],
                        TR.[Copay],
                        TR.[Net due From Insurance],
                        TR.[340B Price],
                        TR.[340B Packet Size],
                        TR.[340B Cost for this RX],
                        TR.[Cardinal Price],
                        TR.[Packet Size],
                        TR.[Dispensing Fees]
                        ,PT.Rebill

from [TransactionReport] TR  
inner Join Plntrak PT  
on  PT.RxNbr  = CONVERT(Int, TR.RxNbr)
where PT.Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
AND   YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35058174
You cant use inner join
select		
			TR.[Last Name],
			TR.[First Name],
			TR.[Last 4 Digit of SSN],
			TR.[ZipCode],
			TR.[County],
			TR.[340B],
			TR.[DOB],
			TR.[Client ID],
			TR.[Insurance Status],
			TR.[PlanId],
			TR.[Generic Code],
			TR.[Drug Name],
			TR.[NDC],
			TR.[RxNbr],
			TR.[Fill Date],
			TR.[Dispensed Qty],
			TR.[Refills],
			TR.[Days Supplied],
			TR.[Plan Allows],
			TR.[Copay],
			TR.[Net due From Insurance],
			TR.[340B Price],
			TR.[340B Packet Size],
			TR.[340B Cost for this RX],
			TR.[Cardinal Price],
			TR.[Packet Size],
			TR.[Dispensing Fees],
			PT.Rebill
from [TransactionReport] TR  
left Join (select MAX(RxNbr) RxNbr, Max(Filldate) Filldate
           from Plntrak 
           where  Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
                  AND YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')
           group by RxNbr, Filldate) PT on  PT.RxNbr  = CONVERT(Int, TR.RxNbr)

Open in new window

0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 200 total points
ID: 35063354
My mistake, you need to add Rebill to the query

(And I'm assuming you want to show the most recent values)

select		
			TR.[Last Name],
			TR.[First Name],
			TR.[Last 4 Digit of SSN],
			TR.[ZipCode],
			TR.[County],
			TR.[340B],
			TR.[DOB],
			TR.[Client ID],
			TR.[Insurance Status],
			TR.[PlanId],
			TR.[Generic Code],
			TR.[Drug Name],
			TR.[NDC],
			TR.[RxNbr],
			TR.[Fill Date],
			TR.[Dispensed Qty],
			TR.[Refills],
			TR.[Days Supplied],
			TR.[Plan Allows],
			TR.[Copay],
			TR.[Net due From Insurance],
			TR.[340B Price],
			TR.[340B Packet Size],
			TR.[340B Cost for this RX],
			TR.[Cardinal Price],
			TR.[Packet Size],
			TR.[Dispensing Fees],
			PT.Rebill
from [TransactionReport] TR  
left Join (select MAX(RxNbr) RxNbr, Max(Filldate) Filldate, Max(Rebill) Rebill
           from Plntrak 
           where  Filldate BETWEEN YEAR('01/01/2011')*1000 + DATEPART(dy, '01/01/2011')              
                  AND YEAR('01/31/2011')*1000 + DATEPART(dy, '01/31/2011')
           group by RxNbr, Filldate) PT on  PT.RxNbr  = CONVERT(Int, TR.RxNbr)

Open in new window

0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 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