Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Remove Duplicates in Self-Join Query

Posted on 2010-09-20
5
Medium Priority
?
850 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

604 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