Solved

Remove Duplicate Column Values in Complex Join Query

Posted on 2010-09-16
5
326 Views
Last Modified: 2012-05-10
I have the attached query working;
I am trying to display the grid in the attached image.
This is a grid of fuel pump readings from one day to the previous day so i join the table to itself.
Most of the fields that I need are in one table so that part is OK.

Next, I need to join another table to pick up the deliveries for a particular date in the grid.
This will mostly be just one day in the range.
However, when I join the table I get an entire column of the same values; when I would simply like to show the value for the particular day.
Image 13 shows the duplicates I am getting in the GALLONS DELIVERED column (quantity_received_fw); and Image 14 shows what I would like to get - the one delivery for the particular date: in this case, 5999.00 for 8/2/2010;
My attached query here is hard-coded to show output-
How can I write this query to ONLY get the ONE delivery value for the particular day?
thanks  13 14
SELECT TOP 100 PERCENT

		v.TANK_ID_FW,

		(v.ranking % 10 + 1)AS "ID",	

		CONVERT(VARCHAR(8), a.transaction_date_fw, 1) AS date,

		v.tank_gallons_reading_fw AS startInv,

		GallonsPumped=ABS(a.pump1_meter_fw-v.pump1_meter_fw) +

		ABS(a.pump2_meter_fw-v.pump2_meter_fw) +

		ABS(a.pump3_meter_fw-v.pump3_meter_fw) +

		ABS(a.pump4_meter_fw-v.pump4_meter_fw) +

		ABS(a.pump5_meter_fw-v.pump5_meter_fw) +

		ABS(a.pump6_meter_fw-v.pump6_meter_fw) +

		ABS(a.pump7_meter_fw-v.pump7_meter_fw), 

		v.tank_gallons_reading_fw-(ABS(a.pump1_meter_fw-v.pump1_meter_fw) +

		ABS(a.pump2_meter_fw-v.pump2_meter_fw) +

		ABS(a.pump3_meter_fw-v.pump3_meter_fw) +

		ABS(a.pump4_meter_fw-v.pump4_meter_fw) +

		ABS(a.pump5_meter_fw-v.pump5_meter_fw) +

		ABS(a.pump6_meter_fw-v.pump6_meter_fw) +

		ABS(a.pump7_meter_fw-v.pump7_meter_fw))

		AS bookInvGallons,	

		a.tank_inches_reading_fw AS endStickInv,

		v.tank_gallons_reading_fw-

		(ABS(a.pump1_meter_fw-v.pump1_meter_fw) +

		ABS(a.pump2_meter_fw-v.pump2_meter_fw) +

		ABS(a.pump3_meter_fw-v.pump3_meter_fw) +

		ABS(a.pump4_meter_fw-v.pump4_meter_fw) +

		ABS(a.pump5_meter_fw-v.pump5_meter_fw) +

		ABS(a.pump6_meter_fw-v.pump6_meter_fw) +

		ABS(a.pump7_meter_fw-v.pump7_meter_fw))

		AS bookInvEnd,	

		a.tank_gallons_reading_fw AS endInv,

		v.tank_inches_reading_fw AS startStickInv,

		a.tank_gallons_reading_fw-

		(ABS(a.pump1_meter_fw-v.pump1_meter_fw) +

		ABS(a.pump2_meter_fw-v.pump2_meter_fw) +

		ABS(a.pump3_meter_fw-v.pump3_meter_fw) +

		ABS(a.pump4_meter_fw-v.pump4_meter_fw) +

		ABS(a.pump5_meter_fw-v.pump5_meter_fw) +

		ABS(a.pump6_meter_fw-v.pump6_meter_fw) +

		ABS(a.pump7_meter_fw-v.pump7_meter_fw))

		AS bookInvEnd2,	

		a.tank_gallons_reading_fw-(v.tank_gallons_reading_fw-(ABS(a.pump1_meter_fw-v.pump1_meter_fw) +

		ABS(a.pump2_meter_fw-v.pump2_meter_fw) +

		ABS(a.pump3_meter_fw-v.pump3_meter_fw) +

		ABS(a.pump4_meter_fw-v.pump4_meter_fw) +

		ABS(a.pump5_meter_fw-v.pump5_meter_fw) +

		ABS(a.pump6_meter_fw-v.pump6_meter_fw) +

		ABS(a.pump7_meter_fw-v.pump7_meter_fw)))

		AS overage,

		quantity_received_fw,

		'0.06' AS 'Water'--,

 

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 v.TANK_ID_FW=c.TANK_ID_FW 





WHERE SUBSTRING(v.[TANK_ID_FW],7,3)='UL-' 

AND a.TRANSACTION_DATE_FW >= '2010-07-29' 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' 

AND a.RECORD_number_FW BETWEEN 909 AND 918

Open in new window

0
Comment
Question by:jtrapat1
  • 3
  • 2
5 Comments
 
LVL 2

Accepted Solution

by:
ymiossec earned 500 total points
ID: 33696727
Hi,

If we suppose that one table X contains delivery quantity, delivery date and tank_id, you must have a left join as this :

left joint Delivery_Table on  
ON v.TANK_ID_FW= X.Tank_Id
and v.transaction_date_fw = Delivery_Date
0
 

Author Comment

by:jtrapat1
ID: 33707949
hi-
thanks; this seems to work;
one last thing...
i need to sum the column of total gallons pumped;
can you help with this?
currently, i am using this as a stored procedure to a reporting services report.
i simply add a footer and put the sum in there;
but, i also want to display the sum elsewhere on the report.
can you help?
thanks.
john
0
 
LVL 2

Expert Comment

by:ymiossec
ID: 33708108
you can do a :
select sum(GallonsPumped) from (<your query>)

replace <your query> with the same query up there, keeping only the fields you need for getting the "GallonsPumped" field
0
 

Author Comment

by:jtrapat1
ID: 33716850
ymiossec:
thanks for the help
but im still having trouble getting the results;
can you explain this a little more?
I'm trying to select a SUM() form a subquery but I keep getting:
Incorrect syntax near the keyword 'SELECT'.

I tried enclosing the whole query in parenthese, givingit an alias but it still doesnt work.
Can you explain?
which fields would I need in addition to the SUM() outside of my subquery?
thanks
john
0
 

Author Comment

by:jtrapat1
ID: 33717061
ymiossec:
disregard that last question;
I got the sum working;
But, it would not would unless I gave the subquery an alias;
Even though the alias was never referenced, it would not work.

attached is my syntax  that i used;
now i just have to write it dynamically;
thanks for your help.
john
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

11 Experts available now in Live!

Get 1:1 Help Now