Solved

Remove Duplicates in Self-Join Query

Posted on 2010-09-20
5
836 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
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now