Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 862
  • Last Modified:

Remove Duplicates in Self-Join Query

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
jtrapat1
Asked:
jtrapat1
1 Solution
 
celdridgeMadmanCommented:
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
 
mcmonapCommented:
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
 
tabishCommented:
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
 
RCM01Commented:
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
 
jtrapat1Author Commented:
thanks for the help-
this was the problem -I knew there was some extra criteria i needed to include.

john
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now