?
Solved

Remove Duplicates in Self-Join Query

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

762 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