Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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