Solved

Remove Duplicates in Self-Join Query

Posted on 2010-09-20
5
844 Views
Last Modified: 2012-05-10
I need help with the following query;
This stored procedure takes parameters and generates a report of fuel pump meter listings for a 10-day date range.  All of my data is in one table.
I'm using a self-join so I can get the previous day's readings but I can't seem to get the unique values.
I've attached two images - one of the output I would like to get and one of the duplicate data.
I am getting three records where I would like to return only one record.
I am getting three because there are three fuel types in the main table: UL, E85 and R.
I use a ranking of the transaction_date_fw field.

Please help.
John

 incorrect
select * 
FROM 
(SELECT RECORD_number_FW, 
		tank_id_fw,
		tank_gallons_reading_fw,
		tank_inches_reading_fw,
		archive_status_fw,
		pump1_meter_fw,
		pump2_meter_fw,
		pump3_meter_fw,
		pump4_meter_fw,
		pump5_meter_fw,
		pump6_meter_fw,
		pump7_meter_fw,
			ranking=(SELECT Count(DISTINCT transaction_date_fw) 
				FROM USER_DEFINED56_FW u 
				WHERE u.transaction_date_fw<=L.transaction_date_fw) 
				FROM USER_DEFINED56_FW L) v
			LEFT JOIN 
				(SELECT RECORD_number_FW, 
				ROUND(tank_gallons_reading_fw,10,2) AS tank_gallons_reading_fw,
				tank_inches_reading_fw,
				pump1_meter_fw, 
				pump2_meter_fw,
				pump3_meter_fw,
				pump4_meter_fw,
				pump5_meter_fw,
				pump6_meter_fw,
				pump7_meter_fw,
				transaction_date_fw,
					ranking=(SELECT Count(DISTINCT [transaction_date_fw]) 
						FROM USER_DEFINED56_FW u		
						WHERE u.transaction_date_fw <= L.transaction_date_fw)
				FROM USER_DEFINED56_FW L) a 
				ON (a.ranking=v.ranking+1) 
LEFT JOIN [FUEL_PRICES_FW] c  
ON c.TANK_ID_FW=v.TANK_ID_FW 
AND c.last_filled_fw =a.transaction_date_fw  
WHERE SUBSTRING(v.[TANK_ID_FW],7,3)='UL-' 
AND a.TRANSACTION_DATE_FW >= '2010-07-30' and a.TRANSACTION_DATE_FW <= '2010-08-12' 
--AND c.TRANSACTION_TYPE_FW='DELIVERY'  
AND RIGHT(v.[TANK_ID_FW],1)=2 
AND v.[ARCHIVE_STATUS_FW]='N' 
ORDER BY a.transaction_date_fw

Open in new window

correct.gif
0
Comment
Question by:jtrapat1
[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 3

Expert Comment

by:celdridgeMadman
ID: 33720919
You could try summarising by by and using either Max() or Sum() on the column you wish to aggregate.

Something LIKE this -
select id, date, MAX(startInc), MAX(GallonsDelivered), MAX(TankTotal), MAX(GallonsPumped), MAX(bookIncGallons), MAX(endStickInchesl...), MAX(endStickI...), MAX(overage), MAX(WATER)

From (...)

GROUP BY id, date

Open in new window

0
 
LVL 15

Expert Comment

by:mcmonap
ID: 33721427
Hi jtrapat1,

Can you post some (helpful) sample of the output from USER_DEFINED56_FW, this can be used to validate your existing query.  One thing that stands out to me is that you are joining [FUEL_PRICES_FW] to both tables but the where clause is rem'd out.
0
 
LVL 4

Accepted Solution

by:
tabish earned 500 total points
ID: 33721815
I think you need to join a and v on tank_id_fw as well.

Like a ON v.tank_id_fw = a.tank_id_fw AND a.ranking = v.ranking + 1

Please try the following:
select * 
FROM 
(SELECT RECORD_number_FW, 
		tank_id_fw,
		tank_gallons_reading_fw,
		tank_inches_reading_fw,
		archive_status_fw,
		pump1_meter_fw,
		pump2_meter_fw,
		pump3_meter_fw,
		pump4_meter_fw,
		pump5_meter_fw,
		pump6_meter_fw,
		pump7_meter_fw,
			ranking=(SELECT Count(DISTINCT transaction_date_fw) 
				FROM USER_DEFINED56_FW u 
				WHERE u.transaction_date_fw<=L.transaction_date_fw) 
				FROM USER_DEFINED56_FW L) v
			LEFT JOIN 
				(SELECT RECORD_number_FW,
                                     tank_id_fw,
				ROUND(tank_gallons_reading_fw,10,2) AS tank_gallons_reading_fw,
				tank_inches_reading_fw,
				pump1_meter_fw, 
				pump2_meter_fw,
				pump3_meter_fw,
				pump4_meter_fw,
				pump5_meter_fw,
				pump6_meter_fw,
				pump7_meter_fw,
				transaction_date_fw,
					ranking=(SELECT Count(DISTINCT [transaction_date_fw]) 
						FROM USER_DEFINED56_FW u		
						WHERE u.transaction_date_fw <= L.transaction_date_fw)
				FROM USER_DEFINED56_FW L) a 
				ON (a.ranking=v.ranking+1) And (v.tank_id_fw = a.tank_id_fw)
LEFT JOIN [FUEL_PRICES_FW] c  
ON c.TANK_ID_FW=v.TANK_ID_FW 
AND c.last_filled_fw =a.transaction_date_fw  
WHERE SUBSTRING(v.[TANK_ID_FW],7,3)='UL-' 
AND a.TRANSACTION_DATE_FW >= '2010-07-30' and a.TRANSACTION_DATE_FW <= '2010-08-12' 
--AND c.TRANSACTION_TYPE_FW='DELIVERY'  
AND RIGHT(v.[TANK_ID_FW],1)=2 
AND v.[ARCHIVE_STATUS_FW]='N' 
ORDER BY a.transaction_date_fw

Open in new window

0
 
LVL 2

Expert Comment

by:RCM01
ID: 33721855
I need some clarification

How does the query results columns correspond to spread sheet columns?
Is the intent for the query to return 10 days of information detailing a particular tank and a particular fuel type?
Does a record in table USER_DEFINED56_FW contain a single days information for a specific tank and fuel type?
What does the column "Ranking" mean?
Does the 'UL-' value indicate the fuel type of unleaded?

My first thoughts depending on the above is that your "where" restriction should be embedded in both V and A, adjusting the date ranges of course. I would also expand the Join columns between V and A to ensure that you are comparing "apples" to "apples". Typically joining based on a count will produce odd results since a count is somewhat random.
0
 

Author Closing Comment

by:jtrapat1
ID: 33780485
thanks for the help-
this was the problem -I knew there was some extra criteria i needed to include.

john
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 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